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");
newSpreadsheet.clearContents();
// 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/vnd.google-apps.spreadsheet");
// Iterate through each file
while (files.hasNext()) {
const file = files.next();
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.