有没有办法加快.deleterow（）

发布于 2025-02-12 17:29:39 字数 926 浏览 0 评论 0原文

我正在研究一个脚本以复制（并删除）电子表格，如果它们满足某些条件，但是有一个问题，我注意到.deleterow（）（）非常非常慢，我想知道是否有一种快速的方法提升我要做的事情（我在复制了我之后删除了一堆不连续的行），因为当前脚本已经计时了，然后它才能删除其应该的所有行。

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

原文

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?

分享到QQ

分享到微博