CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
xitas
xitas

Reputation: 1164

Google Sheet speedup Loops

I have around 5000 rows. Now I want to move data for one sheet to another if their status is Delivered.

This is what I am doing:

function move(){
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var lastSourceRow = sourceSheet.getLastRow();
  var lastSourceCol = sourceSheet.getLastColumn();

  var sourceRange = sourceSheet.getRange(1, 1, lastSourceRow, lastSourceCol);
  var sourceData = sourceRange.getValues();

  var activeRow = 0;
  
  //Loop through every retrieved row from the Source
  for (row = lastSourceRow; row > 1; row--) {
    //IF Column B in this row has 'deal', then work on it.
    if (sourceData[row-1][1] === 'Delivered') { 
      //then push that into the variables which holds all the new values to be returned
      activeSheet.appendRow(sourceData[row-1]);
      //delete current 
      sourceSheet.deleteRow(row);
    }

    Logger.log(row);
  }
}

My problems:

  1. Script is so slow it takes so much time to execute.
  2. As per google policy script timeout in 5 mins.
  3. So the loop has to be started again. So also index is set to null. So I can't check the record at the top if they are delivered or not.

Please can anyone provide me a better solution for this? I search for a faster loop but still no luck.

Upvotes: 0

Views: 43

Answers (1)

Cooper
Cooper

Reputation: 64032

function move(){
  const ash=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  const ssh=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var data=ssh.getDataRange().getValues();
  var d=0;
  var oA=[];
  data.forEach(function(r,i){
    if(r[1]=="Delivered") {
      oA.push(r);
      ssh.deleteRow(i+1-d++);
    }
  });
  ash.getRange(ash.getLastRow()+1,1,oA.length,oA[0].length).setValues(oA);
}

The deletion of the rows always takes time. You could splice them out of the data array and clear the sheet and place them back in with a setvalues(). But if you have formulas this may mess some things up. I'll leave that up to you.

Upvotes: 3

Related Questions