This function runs ok, but for up to 20k rows. What could I change to make it run fast?
const SOURCE_FILE_ID = 'ID';
function getData() {
const sourceSheet = SpreadsheetApp.openById(SOURCE_FILE_ID);
const sourceRng = sourceSheet.getSheetByName('ativcopiar').getRange(1, 1, sourceSheet.getLastRow(), 9);
const sourceValues = sourceRng.getValues();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Ativ.');
var destinationRng = sheet.getRange(1, 1, sheet.getLastRow(), 9);
destinationRng.clearContent();
destinationRng.setValues(sourceValues);
}
Sometimes, it exceeds the time limit.
Appreciate any light! Thanks.
I believe your goal is as follows.
In this case, how about using Sheets API? When Sheets API is used, the process cost can be reduced a little. Ref When Sheets API is used for your script, it becomes as follows.
Before you use this script, please enable Sheets API at Advanced Google services.
function myFunction() {
const SOURCE_FILE_ID = '###';
const sheet = SpreadsheetApp.openById(SOURCE_FILE_ID).getSheetByName('ativcopiar');
const dstSS = SpreadsheetApp.getActiveSpreadsheet();
var dstSheet = dstSS.getSheetByName('Ativ.');
var destinationRng = dstSheet.getRange(1, 1, dstSheet.getLastRow(), 9);
destinationRng.clearContent();
SpreadsheetApp.flush();
const values = Sheets.Spreadsheets.Values.get(dstSS.getId(), "'ativcopiar'!A1:I" + sheet.getLastRow()).values;
Sheets.Spreadsheets.Values.update({values}, dstSS.getId(), "Ativ.", {valueInputOption: "USER_ENTERED"});
}