Search code examples
google-apps-scriptgoogle-sheets

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

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!

Solution

  • 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: