0

I need help, i'm beginner, I want to loop this execution and also include the timeout error to set until all 500 is completed

{

function LetsDoIT() {

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Values'), true);

  spreadsheet.getRange('A1').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Enter'), true);
  spreadsheet.getRange('A1').activate();
  spreadsheet.getRange('\'Values\'!A1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.waitForAllDataExecutionsCompletion;
  spreadsheet.getRange('B1:U1').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Values'), true);
  spreadsheet.getRange('B1').activate();
  spreadsheet.getRange('\'Enter\'!B1:U1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.getRange('A2').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Enter'), true);
  spreadsheet.getRange('A1').activate();
  spreadsheet.getRange('\'Values\'!A2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.waitForAllDataExecutionsCompletion;
  spreadsheet.getRange('B2:U2').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Values'), true);
  spreadsheet.getRange('B2').activate();
  spreadsheet.getRange('\'Enter\'!B2:U2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

....
....
.... upto 500 times

  spreadsheet.getRange('V2').activate();
  var criteria = SpreadsheetApp.newFilterCriteria()
  .whenCellNotEmpty()
  .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(22, criteria);
  spreadsheet.getRange('A1:U500').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Final Data'), true);
  spreadsheet.getRange('A1').activate();
  spreadsheet.getRange('\'Values\'!A1:U500').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    };

}

*********************it takes almost 10 sec to execute in single

Shai
  • 1
  • 1
  • What you really need to do is study the getRange(row,col,numrow,numcol) and getValues()/setValues() methods of the SpreadsheetApp. [link](https://developers.google.com/apps-script/reference/spreadsheet/) – TheWizEd Jan 23 '22 at 15:22

0 Answers0