How do i shorten time this GAS code(search correct column and data paste)-CodePudding

https://i.stack.imgur.com/7VAJk.png

i want to copy data from "dB" sheet A5:A29 and paste to correct column. so i use the script to find the correct column.

there range B2:CX2 have 0(not-correct) or 1(correct) value, so i use 'for' & 'if' BUT!! It's too delay!! i use console.time() and i get 25909ms(timecheck2 value) !!!

please help me.....

here is my code

function save(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('dB');
  
  console.time("timecheck1");
  //find last row
  var copyrangeO = sheet.getRange(5,1,25,1).getValues();
  var lastrowO = copyrangeO.filter(String).length; 
  var copyrange = sheet.getRange(5,1,lastrowO,1);
  console.timeEnd("timecheck1");
  
  //my dB data start "B2". 
  var cv = 1;
  
  //find correct value(1). B2 ~ CX2 (#100)
  console.time("timecheck2");
  for (var i=2; i<101;i  ){
    if(sheet.getRange(2,i).getValue()===1){
      cv = i;
    }
  }
  console.timeEnd("timecheck2");

  //if data isn't correct, cv===1. so error msg print.
  console.time("timecheck3");
  if(cv ===1){
    Browser.msgBox("ERROR")
  }else {
    //data copy and paste.
    var columnToCheck = sheet.getRange(4,cv,1000).getValues();
    var lastrow = getLastRowSpecial(columnToCheck);
    var pasterange = sheet.getRange(lastrow 4,cv);
    copyrange.copyTo(pasterange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    Browser.msgBox(lastrowO   " saved!");
  }
  console.timeEnd("timecheck3");
  
}

CodePudding user response：

The function will spend most of its time in the for loop because it repeats the Range.getValue() call many times. You can speed things up quite a bit by getting all values with one Range.getValues() call, like this:

  let cv = 1;
  console.time("timecheck2");
  sheet.getRange('B2:B100').getValues().flat()
    .some((value, index) => (cv = 2   index) && value === 1);
  console.timeEnd("timecheck2");

Note that this is not a cleanest way of finding cv, but it should help illustrate why you have a performance issue. You may want to do a complete rewrite of the code, using declarative instead of imperative style.

CodePudding user response：

Try this:

I don't know what you're doing in the save because to did not supply the helper function code.

function save(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('dB');
  var vs0 = sh.getRange(5,1,25,1).getValues();
  var lr0 = vs0.filter(String).length; 
  var crg = sh.getRange(5,1,lr0,1);
  var cv = 1;
  const vs1 = sh.getRange(2,2,1,99).getValues().forEach((c,i) => {
    if(c == 1)cv = i   2
  })
  if(cv == 1){
    Browser.msgBox("ERROR")
  }else {
    var vs2 = sh.getRange(4,cv,1000).getValues();
    var lastrow = getLastRowSpecial(vs2);
    var drg = sh.getRange(lastrow 4,cv);
    crg.copyTo(drg, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    Browser.msgBox(lr0   " saved!");
  }
}