0

I am trying to copy a value from the active spreadsheet to another spreadsheet using setValue. Running the script with the line (setValue) just after the comment raises the execution time with more than 10 seconds, compared to when i run it without setting the value of the selected range. Any idea why this is happening ?

function projectionNewProj(){
  const sourceSpreadsheet = SpreadsheetApp.getActive();
  const projection_sheet = SpreadsheetApp.openById("dazMV23sKvnFeFR2jba17gaUKsg8").getSheetByName("Projections Master");
  const active_sheet = sourceSpreadsheet.getActiveSheet();
  const active_row = active_sheet.getActiveCell().getRow();
  const proj_id = active_sheet.getRange(active_row,1).getValue();
  var ui = SpreadsheetApp.getUi();
  
  if (active_sheet.getName()=="Client List" && active_row > 1 && proj_id != "") {
    const pm = active_sheet.getRange(active_row,11).getValue();
    if (pm !==""){
      const client_name = active_sheet.getRange(active_row,2).getValue();
      client_indice = projection_sheet.getRange("A14:A").createTextFinder(client_name).matchEntireCell(true).findAll();
      
      if (client_indice.length != 0){
        proj_indice = projection_sheet.getRange("B14:B").createTextFinder(proj_id).matchEntireCell(true).findNext();
        
        if (proj_indice == null){
          const ins_row = client_indice.pop().getRow();
          projection_sheet.insertRowAfter(ins_row);
          const rowin = projection_sheet.getRange(ins_row+1, 2);
          // The following line adds more than 10 seconds ! 
          rowin.setValue(1254);
        }else{
          ui.alert("This Project already exists");
        }

      }else{
        ui.alert("Client Name does not exist in the Projections Sheet");

      }
    }else{
      ui.alert("Please select a Project manager");
    }
  }else{
    ui.alert("Please select a non-empty project row in Client List Sheet");
  }
Lyesgigs
  • 119
  • 1
  • 12

2 Answers2

1

Google apps script uses a cache.

Every time you do a read, we must first empty (commit) the write cache to ensure that you're reading the latest data (you can force a write of the cache by calling SpreadsheetApp.flush()). Likewise, every time you do a write, we have to throw away the read cache because it's no longer valid. Therefore if you can avoid interleaving reads and writes, you'll get full benefit of the cache.

Since that is the final line, all the actions up and until the setValue will be flushed with that setValue line. Even with the cache flushing, 10s is long and unusual. The reason it is slow might also be because of a bulky spreadsheet.

  • Consider deleting all the empty rows at the bottom and empty columns at the right of that sheet.

  • Consider fixing arrayformulas, as they might break with traditional improper use.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
0

ui.alert keeps your script running until user closes dialog box.

Kos
  • 4,890
  • 9
  • 38
  • 42