Google Sheets script to find & update data on a large list is very slow

Question

hoping someone can set me on the right path. (I think this comes down to me not understanding how to work with client-side arrays?).

The script I've cobbled together looks through a list of clients for a matching name, then updates a bunch of columns based on the data I've entered on another tab. In the end I'd like to be able to do this from another sheet entirely, but the right now it takes 3-4 minutes to loop through the entire list. I suspect what I need to do is grab the whole dataset, modify it and then set the new values, but I'm not really sure if I'm understanding the problem correctly.

function UpdateEntry() {
    var client_name=SpreadsheetApp.getActive().getSheetByName("Rolodex!C2").getValue()
    var addr=SpreadsheetApp.getActive().getSheetByName("Rolodex!C4").getValue()
    var client_phone=SpreadsheetApp.getActive().getRange("Rolodex!E4").getValue()
    var client_fax=SpreadsheetApp.getActive().getRange("Rolodex!E6").getValue()
    var client_WWW=SpreadsheetApp.getActive().getRange("Rolodex!E7").getValue()
      
    var data=SpreadsheetApp.getActive().getSheetByName("Rolodex").getRange("C4").getValue()
    var key=SpreadsheetApp.getActive().getSheetByName("Rolodex").getRange("C2").getValue()

    var database=SpreadsheetApp.getActive().getSheetByName("Database")
    var datalength=database.getDataRange().getValues().length
  
    database.getDataRange().getValues()
  
    for (var i = 1;[i]<=datalength ; i++){
        Logger.log('i: ' + i)
        if(database.getRange([i],[1]).getValue() == client_name){
            database.getRange([i],2).setValue(addr)
            database.getRange([i],3).setValue(client_phone)
            database.getRange([i],4).setValue(client_fax)
            database.getRange([i],5).setValue(client_WWW)
        }
    }
}

Answer 1

I believe your current situation and goal as follows.

• Your script works fine. But the process cost is high.
• You want to reduce the process cost of the script.

Modification points:

• getValue() is the method of Class Range. But at var client_name=SpreadsheetApp.getActive().getSheetByName("Rolodex!C2").getValue(), getValue is used as the method of Class Sheet. In this case, I think that an error occurs at the 1st line of your script.
  • From your question, I thought that getSheetByName might be getRange.
• SpreadsheetApp.getActive() can be used by declaring one time.
• Values of client_name,addr,client_phone,client_fax,client_WWW can be retrieved by one getValues.
• data and key are not used.
• Line of database.getDataRange().getValues() is not used.
• getValue and setValue are used in the loop.
  • In this case, at first, the values are retrieved from the sheet using getValues. And, create the values for putting to the sheet, and then, the created values are put to the sheet using setValues.
  • Here, as an important point, when setValues is used, it is required to be the same length for all column length. Please be careful this.

When above points are reflected to your script, it becomes as follows.

Modified script:

function UpdateEntry() {
  var ss = SpreadsheetApp.getActive();

  // 1. Retrieve values of client_name, addr, client_phone, client_fax, client_WWW from Rolodex sheet.
  var sheet = ss.getSheetByName("Rolodex");
  var [[client_name],,[addr,,client_phone],,[,,client_fax],[,,client_WWW]] = ss.getRange("Rolodex!C2:E7").getValues();
  var ar = [client_name, addr, client_phone, client_fax, client_WWW];
  var arLength = ar.length;

  // 2. Retrieve values from database sheet.
  var database = ss.getSheetByName("Database");
  var values = database.getDataRange().getValues();
  var columnLength = values[0].length;

  // 3. Create values for putting to Database sheet using the values retrieved from Rolodex sheet.
  var res;
  if (columnLength > arLength) {
    res = values.map(r => r[0] == client_name ? ar.concat(r.slice(arLength)) : r);
  } else {
    res = values.map(r => r[0] == client_name ? ar : r.concat(Array(arLength - columnLength).fill("")));
  }
  
  // 4. Put the values to Database sheet.
  database.getRange(1, 1, res.length, res[0].length).setValues(res);
}

Note:

• When you use this modified script, please enable V8 runtime at the script editor.

• If above modified script was not the result you expect, your current script might have not been the result you expect. If my understanding is correct, can you provide the sample input and output you expect? By this, I would like to confirm it.

References:

• Destructuring assignment
• map()
• Conditional (ternary) operator
• getValues()
• setValues(values)
• google-apps-script
  • I think that the links in above page might be useful.