Google Sheets, how to run function every x column?

Question

I have a script in google sheets with the following function:

// Function to get current active sheet-name.
function getsheetName(dummy) {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

Currently this function runs with this code in sheets:

=getsheetname(3:55)

The reference targets my entire sheet.

So whenever I make a change to a cell in my sheet the function runs again. This causes a huge amount of loading everytime i type anything in a new cell. Can i somehow reference the function to only run when i make changes to every x column instead of every cell?

I tried changing the formula but it would just stop updating completely, tried combining strings and using the MOD operator but I couldn't figure it out (keep in mind i am very new at this so probably missing something in my tinkering).

Appreciate any help!

Answer 1

I believe your goal is as follows.

• You want to refresh the custom function of =getsheetname() when the columns "J", "T",,, (every 10 columns).

In this case, how about the following sample script? In order to refresh the function on Google Spreadsheet, I used this method. Ref

Sample script:

In this sample, in order to refresh your custom function, the simple trigger of onEdit is used. Please copy and paste the following script to the script editor of Spreadsheet. And, please save the script. When you use this script, please edit the columns "J", "T" and so on. By this, the script is run.

function onEdit(e) {
  if (e.range.columnStart % 10 != 0) return;
  const functionName = "=getsheetName";
  const temp = "=temp";
  const sheet = e.source.getActiveSheet();
  sheet.createTextFinder(functionName).matchFormulaText(true).replaceAllWith(temp);
  sheet.createTextFinder(temp).matchFormulaText(true).replaceAllWith(functionName);
}

• In this case, 3:55 of =getsheetname(3:55) is not required to be used. You can use just =getsheetname().

• If you changed the function name of your custom function, please modify const functionName = "=getsheetName";.

Reference:

• Simple Triggers