1

I've been attempting to export the entire contents of my spreadsheet. The code works fine, but it displays an error message that says Maximum execution limit exceeded. According to my research, the maximum execution time is only 6 minutes. I'm new to this and am still trying to figure it out. Could you kindly help me in determining a possible solution for this?

Here's the code that I'm currently using...

function PrintMultiple() {

  const srcSs = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = srcSs.getSheetByName("TEMPLATE");
  const values = sheet.getRange("C2").getDataValidation().getCriteriaValues()[0].getValues().flat().filter(String);
  const dstSs = SpreadsheetApp.create("tempSpreadsheet");

  SpreadsheetApp.getActive().toast("About to take some action... Please wait...");

  values.forEach(v => {
    sheet.getRange("C2").setValue(v);
    SpreadsheetApp.flush();
    const tempSheet = sheet.copyTo(srcSs);
    const range = tempSheet.getDataRange();
    range.copyTo(range, {contentsOnly: true});
    tempSheet.getRange("B2:2").clear().clearDataValidations();
    tempSheet.getDrawings().forEach(e => e.remove());
    tempSheet.deleteColumn(1);
    tempSheet.deleteRow(1);
    tempSheet.deleteRow(2);
    tempSheet.deleteRow(3);
    tempSheet.copyTo(dstSs);
    srcSs.deleteSheet(tempSheet);
  });
  dstSs.deleteSheet(dstSs.getSheets()[0]);
}
Tanaike
  • 181,128
  • 11
  • 97
  • 165
cjwrk
  • 255
  • 1
  • 13
  • Although I'm not sure about your actual Spreadsheet, I proposed the modified script for reducing the process cost as an answer. Could you please confirm it? If that was not the direct solution to your issue, I apologize. – Tanaike Oct 27 '21 at 02:08

1 Answers1

1

I believe your goal is as follows.

  • You want to reduce the process cost of your script.

From your script, when the length of values is large, I thought that the process of copy might be the high cost. So, in this case, how about using Sheets API? I thought that when Sheets API is used, the process cost of your script might be able to be reduced. When Sheets API is used for your script, it becomes as follows.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services.

function PrintMultiple() {
  const ss = SpreadsheetApp.getActiveSpreadsheet().copy("tempSpreadsheet");
  const sheet = ss.getSheetByName("TEMPLATE");
  const sourceSheetId = sheet.getSheetId();
  sheet.getDrawings().forEach(e => e.remove());
  const values = sheet.getRange("C2").getDataValidation().getCriteriaValues()[0].getValues().flat().filter(String);
  const requests1 = values.flatMap((v, i) => {
    const sheetId = 123456 + i;
    return [
      { duplicateSheet: { newSheetName: `page${i + 1}`, sourceSheetId, newSheetId: sheetId, insertSheetIndex: i + 2 } },
      { updateCells: { range: { sheetId: sheetId, startRowIndex: 1, endRowIndex: 2, startColumnIndex: 2, endColumnIndex: 3 }, rows: [{ values: [{ userEnteredValue: { numberValue: v } }] }], fields: "userEnteredValue.numberValue" } },
    ];
  });
  Sheets.Spreadsheets.batchUpdate({ requests: requests1 }, ss.getId());
  const requests2 = values.flatMap((_, i) => {
    const sheetId = 123456 + i;
    return [
      { copyPaste: { source: { sheetId }, destination: { sheetId }, pasteType: "PASTE_VALUES" } },
      { deleteDimension: { range: { sheetId, startIndex: 0, endIndex: 3, dimension: "ROWS" } } },
      { deleteDimension: { range: { sheetId, startIndex: 0, endIndex: 1, dimension: "COLUMNS" } } },
    ];
  });
  Sheets.Spreadsheets.batchUpdate({ requests: requests2 }, ss.getId());
  ss.deleteSheet(sheet);
  ss.deleteSheet(ss.getSheets()[0]);
}
  • In this modification, the method of batchUpdate of Sheets API is used for copying values and deleting rows and columns.
  • When this script is run, "tempSpreadsheet" Spreadsheet is created to the root folder.

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165