1

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!

Axxi
  • 193
  • 1
  • 10
  • 1
    I have to apologize for my poor English skill. Unfortunately, I cannot understand `Can i somehow reference the function to only run when i make changes to every x column instead of every cell?`. Can I ask you about the detail of it? – Tanaike May 14 '22 at 12:00
  • of course, no problems. I'm also probably explaining it poorly. Right now the function runs everytime I type in any cell that is in row 3-55. But I only want it to run when i type in every 10th column. So for example, everytime i type in column J, then function should run again, and same thing if i type in Column T etc. Does this make better sense? If not then maybe i can make a share a google spreadsheet to show it. – Axxi May 14 '22 at 12:09
  • Thank you for replying. From your replying, I proposed an answer. Could you please confirm it? If I misunderstood your question and that was not useful, I apologize. – Tanaike May 14 '22 at 12:24

1 Answers1

2

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Luckily i know how to use scripts and have been able to implement this, but it unfortunately did not seem to work for me :( Have you had the chance to try it out yourself? – Axxi May 14 '22 at 13:08
  • @Axxi Thank you for replying. I apologize for the inconvenience. About `Have you had the chance to try it out yourself?`, before I posted this sample script, I tested this by editing the columns "J", "T", and so on and I confirmed that the script worked. So I posted this. I deeply apologize that I had tested my sample script. In order to correctly understand your current situation, can you provide the detailed flow for correctly replicating it? I would like to confirm it. – Tanaike May 14 '22 at 13:12
  • @Axxi For example, if you have already used `onEdit` function, as a test case, please rename the existing `onEdit` function name and test it again. At that time, please edit the cell of columns "J", "T". By this, the script is automatically run by the simple trigger. – Tanaike May 14 '22 at 13:29
  • Alright, i will try this as soon as i get home. Thanks for helping me btw! – Axxi May 14 '22 at 14:49