Google Apps Script/Javascript: Faster/better solution for dropdown selection that adds rows beneath?

Question

I'm still very new to Javascript, but I've been learning a lot.

I'm creating an intake form on Google Sheets where the user can make selections, and then depending on the selection on some dropdown rows, the sheet unhides certain rows beneath where the user can fill in more information. The function is triggered "on change", so I'm assuming that every time the sheet senses a change has been made, it then executes the function to hide or unhide rows.

As you can see in the attached GIF, my script works pretty well, but it is terribly slow, with some response times approaching 3-4 seconds before the script unhides the appropriate rows.

The code that I have so far works like this:

function selectionUnHidesRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const values = sheet.getDataRange().getValues();
// Someone else gave me the following code to create variables from whatever values are in column A (the "header" column) and then grabs the value to the right of it in column B (the "input" column)
  const conversionObj = {
    "Client Marital Status": "clientMaritalStatus",
    "Client's Spouse's Name": "clientSpouseName",
    "How many children?": "numChildren",
  }
  const obj = values.reduce((o, [a, b]) => {
    if (conversionObj[a]) {
      o[conversionObj[a]] = b;
    }
    return o;
  }, {});
  const {clientMaritalStatus, clientSpouseName, numChildren} = obj;

// This looks through the sheet and finds the required text from Column A, here "Client's Spouse's Name"
  const clientSpouseTextFinder = sheet.createTextFinder("Client's Spouse's Name").findNext();
// once it's found the text, it gets the row
  const clientSpouseRow = clientSpouseTextFinder.getRow();

  switch(clientMaritalStatus) {
    case "married":
      sheet.showRows(clientSpouseRow); // if the dropdown next to "Client Marital Status" says "married", then show the row below that asks for the spouse's name
      break;
    default:
      sheet.hideRows(clientSpouseRow); // if the dropdown has anything other than "married", then hide the row below
    break;
  }
}

Are there potentially any faster ways of achieving the same thing? I tried to figure out how to append rows instead of hiding/unhiding rows, but it seemed more complicated and possibly would take longer to add rows and input values into the requisite cells.

How the onChange works depends upon the changeType but most triggers do not trigger on changes caused by other scripts or cell formulas. — Cooper, May 06 '23 at 23:08
@Cooper That’s good to know that it’s about typical, thank you. Are you aware of any possibly faster alternatives? — sethg, May 06 '23 at 23:17
In order to correctly test your script, can you provide a sample Spreadsheet? — Tanaike, May 07 '23 at 00:27
That's the kind of thing you give up when you join a server based community — Cooper, May 07 '23 at 00:29

score 1 · Accepted Answer · answered May 08 '23 at 15:13

It seems to me that the problem might be in these two places:

  const conversionObj = {
    "Client Marital Status": "clientMaritalStatus",
    "Client's Spouse's Name": "clientSpouseName",
    "How many children?": "numChildren",
  }

Depending on the amount of data the "clientMaritalStatus" has this section of code:

switch(clientMaritalStatus) {
    case "married":
      sheet.showRows(clientSpouseRow); // if the dropdown next to "Client Marital Status" says "married", then show the row below that asks for the spouse's name
      break;
    default:
      sheet.hideRows(clientSpouseRow); // if the dropdown has anything other than "married", then hide the row below
    break;
  }

might actually take a long time to load.

The other thing that is most definitely making things slow is this line:

 const clientSpouseTextFinder = sheet.createTextFinder("Client's Spouse's Name").findNext();

I believe that this has the biggest impact on the code, the TextFinder class will always go through the entire sheet looking for a those cells that match the provided text, I believe a better option would be to have something like this (Just an example, the code will not work!):


 for(var i = source.getLastRow(); i > -1; i--) { //assuming that the marital status data is coming from a different sheet and that a variable "source" was created above to determinewhere this is coming from
      if(source[i].toString == "married"){
        var rowToHide = i;
      }
  sheet.hideRows(rowToHide)

The main difference is that by providing a specific range the code will only run until it finds what you are looking for instead of having to go through the entire sheet.

Please keep in mind that the code provided just above is only meant as a proof of concept and it will most definitely not work for you, something more specific to your situation can definitely be done but in order to do so we would need some sample data, no specific names or anything, just something that would have the same structure as the sheet and dataset you are using for your code with that information I will be happy to update my answer so it works better for you!

In any case I hope you can find this useful!

Very useful, thank you! I'll try and optimize as much as possible — sethg, May 10 '23 at 03:44
Sure thing! by the way one thing I forgot to mention on the answer is that a for loop is actually faster in reverse for(var i = source.getLastRow(); i > -1; i--) you can read more about the reason why here: https://stackoverflow.com/a/13136778/17987690 — Rene Olivo, May 10 '23 at 12:49

Google Apps Script/Javascript: Faster/better solution for dropdown selection that adds rows beneath?

1 Answers1