I'm working on a script to copy (and delete) rows from a spreadsheet if they fulfill certain criteria, but theres an issue, ive noticed that .deleteRow() is VERY VERY VERY SLOW, and im wondering if theres a way to speed up what i gotta do (delete a bunch of non-contiguous rows after ive copied them) because currently the script is timing out before it can delete all the rows its supposed to.
function copyIfConditions() {
const spreadSheet = SpreadsheetApp.getActive();
const sourceSpreadSheet = spreadSheet.getSheetByName("NUEVOS INGRESOS Y EXPEDIENTES EN TRAMITE");//Changed sheet name
const activeSpreadSheet = spreadSheet.getSheetByName("CASOS ARCHIVADOS/FINALIZADOS");
const spA = ["Finalizado_Archivado", "Remitida_a_otro_juzgado_conexidad", "Remitida_UACF", "Remitida_a_otro_juzgado_por_recusacion", "Remitida_a_otro_Juzgado_por_cuestion_de_turno", "Remitida_a_otros_fueros"];
let ingresos_lastRow = sourceSpreadSheet.getLastRow()
let ingresos_lastColumn = sourceSpreadSheet.getLastColumn()
let archivados_lastRow = activeSpreadSheet.getLastRow()
if (ingresos_lastRow == 1) {
SpreadsheetApp.getActive().toast('No data!', 'End of script 🗃️');
return;
}
const vs = sourceSpreadSheet.getRange(2, 1, ingresos_lastRow - 1, ingresos_lastColumn).getValues();
const archiveData = [];
// const deleteRowsData = [];
let d = 0;//delete counter
vs.forEach((r, i) => {
if (~spA.indexOf(r[34])) {
archiveData.push(r);
//deleteRowsData.push(i + 2 - d++);
}
});
activeSpreadSheet.getRange(archivados_lastRow + 1, 1, archiveData.length, archiveData[0].length).setValues(archiveData);
for (let i = 0;i in deleteRowsData;i++){
//sourceSpreadSheet.deleteRow(deleteRowsData[i])
};
console.log(deleteRowsData);
}
Code used to be a bit different, simply deleting the row like this after doing archiveData.push()
if (~spA.indexOf(r[34])) {
archiveData.push(r);
sourceSpreadSheet.deleteRow(i + 2 - d++);
}
As you can imagine, that was even worse. Basically, is there a way to make it so deleting those rows is faster so i dont fall victim to Google's execution time limit?