Modify Appscript so that Execution Time will not be Exceeded

  Kiến thức lập trình

Help me please. I am trying to consolidate all data from different sheets located in folder id 16jQGy_NZJWazugBTfkv48s-egT48RsRI. However, there are too many data in every sheets, so execution time exceeded. Can you help me modify the script so that execution time will not be exceeded? I can’t give the files since they are confidential. At least you can help me where to edit. Thank you!

function mergeDataFromSheets() {

  // Get the folder ID where the source spreadsheets are located
  const folderId = "16jQGy_NZJWazugBTfkv48s-egT48RsRI";

  // Get the active spreadsheet (the master spreadsheet)
  const masterSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

    // Set the new spreadsheet ID where the "DTR_Merge" sheet is located
  const newSpreadsheet = masterSpreadsheet.getSheetByName("DTR_Merge");

  // Get the target cell value from the "Teacher Rate" sheet
  const targetCell = masterSpreadsheet.getSheetByName("Teacher Rate").getRange("Q3").getValue();

  // Get the folder containing the source spreadsheets
  const folder = DriveApp.getFolderById(folderId);

  // Get all files in the folder with the specified MIME type (Google Sheets)
  const files = folder.getFilesByType("application/");

  // Iterate through each file
  while (files.hasNext()) {
    const file =;
    const spreadsheet = SpreadsheetApp.openById(file.getId());

    // Get the sheet with the specified name from the source spreadsheet
    const sourceSheet = spreadsheet.getSheetByName(targetCell);

    if (sourceSheet) {
      // Calculate the last row with data in the source sheet
      const lastRow = sourceSheet.getRange("C3:C").getValues().filter(String).length + 2;

      // Get the data range from the source sheet
      const dataRange = sourceSheet.getRange("A3:J" + lastRow);
      const dataValues = dataRange.getValues();

      // Append the data to the "DTR_Merge" sheet in the new spreadsheet
      newSpreadsheet.getRange(newSpreadsheet.getLastRow() + 1, 1, dataValues.length, dataValues[0].length).setValues(dataValues);

I have a script in appscript that works well but execution time exceeded.

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website