Script to sort, export sheet as PDF, and save it to a specific folder in my Google Drive [Google Sheets] [Apps Script]

joeB

I have a spreadsheet in Google Sheets that I use to manage my personal finance monthly.

I am trying to write a script that will allow me to sort certain ranges of the sheet in ascending order and then export the sheet as PDF.

Here is my current script (with some sensitive values replaced with generics for privacy):

function sortAndExportPdf() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  // var rangesToSort = ["R3:U35", "W3:Z35", "AB3:AE35"];
  
  // for now, only R3:U15 contains non-empty cells
  //    (this should be calculated dynamically as it might vary)
  var numRowsWithData = sheet.getRange("R3:R35").getValues().filter(String).length; // find number of rows with non-empty cells
  var r = sheet.getRange(3, 18, numRowsWithData + 2, 4); // range R3:U15 which is what contains the non-empty cells
  r.sort({column: 1, ascending: true}); // causing "Exception: Cell reference out of range"
  
  // sort the other ranges (W3:Z35 & AB3:AE35) similarly to the previous step

  // Save file
  var folderId = 'folder_id_value';
  var folder = DriveApp.getFolderById(folderId);
  var pdfFile = folder.createFile(spreadsheet.getAs('application/pdf'));
  
  // Assign file name
  var year = new Date().getFullYear();
  var month = sheet.getRange('B2').getValue(); // B2 contains the month name
  var fileName = year + '-' + month + '.pdf'; // for example "2023-June.pdf"
  pdfFile.setName(fileName);
}

My issue is that I am getting an error "Exception: Cell reference out of range" from the sorting step and I am not sure at all why is that.

Some resources I consulted:

  1. Class Sheet | Apps Script
  2. Class Range | Apps Script

Can someone provide some pointers to help me troubleshoot further or suggest a solution?

Thanks!

Tanaike

From the error message of Exception: Cell reference out of range, when I saw your script, it seems that the range is var r = sheet.getRange(3, 18, numRowsWithData + 2, 4);. In this case, it's "R3:U". But, at r.sort({column: 1, ascending: true});, you are trying to use column: 1. In the case of sort(sortSpecObj), it seems that column: 1 is column "A". "R3:U" doesn't include column "A". By this, such an error occurs.

If your column: 1 is the 1st column of "R3:U", namely, column "R", it seems that column: 18 is required to be used. So, in your script, how about the following modification?

From:

r.sort({column: 1, ascending: true});

To:

r.sort({column: 18, ascending: true});
  • In this case, sorts ascending by column "R".
  • For example, if you want to sort ascending by column "U", please modify it to r.sort({column: 21, ascending: true});.
  • By the way, it seems that r.sort({column: 18, ascending: true}); is the same with r.sort([18]);.

Reference:

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Upload file to my google drive with Google Apps Script (NO FORM IN GOOGLE)

How to Save a JSON file to Google Drive using Google Apps Script?

Google sheet: Export range as PDF and save it in a specific sub folder

Export a Google Sheet to Google Drive in Excel format with Apps Script

Google Script to email PDF from specific google sheet sends all sheets on some sheets, not others

Google Script App - Save file in specific folder

Google Apps script move My drive Files to Team drive folder based on file name

setName when exporting a PDF from Google Sheets with Google Apps Script

save a sheet from spreadsheet as pdf in same google drive folder

Export google spreadsheet to PDF, then save the PDF to drive with a specific name

Google Apps Script automatic (on edit) sort (specific sheets only)

Want to create google sheet,docs ,folder using apps script for my google sites

Automatically download Google Sheet into a specific folder by script

Gsheets Apps script: Make a copy of a Spreadsheet in a specific folder of Google Drive

Google Apps Script how to export specific sheet as csv format

Need to Query Google Sheet for specific data from Google Apps Script

Save pdf file in google drive then get the url download link to specific column using google script

Google Sheet Script - save each tab into seperate file in specific folder

Want the link of the newly created template folder in google drive to add in the specific cell of google sheet through apps script

HTML to Image from Google Sheets to Google Drive on Google Apps Script

Upload PDF file in Google sheet using Google apps script

Google Apps Script - EXPORT all sheets INDIVIDUALLY to PDF

Problem trying to save my google sheet as a pdf to my drive folder

Google Apps Script - How to change the code to create Google Sheets file in active Google Drive Shared folder rather than root (My Drive) folder?

How do I get 5 rows from the end of my sheet in Google Sheets using Apps Script?

Apps Script - Export JSON properly into Google Sheet

Can I export my data to a Google Doc from Google Sheets using Apps Script?

Use script only on specific sheet not on all sheets in Google sheets

Google Sheets script to sort entire sheet in descending order