Google Script Google Sheets don't update function on load

Bugs Bunny 0
Viewed 28

I have a custom script running in my sheet, and the script basically requires values from another cell which is dependant on another cell.
Example:

Cell 1 Cell 2 Cell 3
100 Cell 1*2 Cell 2 + 100

Cell 2 will never change in the script, it just needs to be updated once when Cell 1 is created. The problem is that whenever I open the sheet again, it'll try to do my custom functions together. Cell 3 can't load because cell 2 isn't set yet. Is there a way to never have these functions run again, because re-opening the sheet will do this cycle and throw error on cell 3 (in this example, in my script it's a couple of cells).

google-sheets google-apps-script
1 Answers

I believe your goal is as follows.

  • You want to run only one time when a value is put to "Cell 1".
  • From your table, I guessed that "Cell 1", "Cell 2" and "Cell 3" might be the columns "A", "B" and "C", respectively.

In this case, how about using the OnEdit trigger? When the OnEdit trigger is used, when a value is put into "Cell 1", a value can be put into a cell as the fixed value. By this, even when the Spreadsheet is reopened, the value is not changed. I thought that this might be useful for your situation. When this is reflected in a sample script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet, and save the script. When you use this script, please edit a cell of column "A". By this, the calculated value is put to column "B" as the fixed value.

function onEdit(e) {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName || range.columnStart != 1 || range.rowStart == 1) return;
  const value = range.getValue(); // This is a value from "Cell 1".

  // do something.
  // Please set your current script using the value of "Cell 1".

  const result = value; // Please set your calculated result by your script.
  range.offset(0, 1).setValue(result);
}
  • In this sample, as sample, this script checks column "A" and the calculated value is put to column "B". When you want to use other cells, please modify this script.

Reference:

Tanaike0
Related