Trying to understand setFormula vs setValue behaviour

Question

This is only my third post here. I have never coded, I have no skills and I am just trying to learn some concepts so I can "fix" a Google Apps Script.

Problem: I would like to understand setFormula and setValue behaviour, and why cell value is updated or not.

I am using UrlFetchApp.fetch to get some values from an URL and the setting that value in a cell.

I am writing the value with setFormula or setValue .

The functions are triggered manually from a menu in the sheet (not coded in the script below)

So I fetch data from "Url" and write them on the cell.

First run of the Function:

If data in URL is: "A", then:

--> setFormula writes "A" --> setValue writes "A"

Second run of the Function:

Data in URL has changed from "A" to "B", then:

--> setFormula does not overwrites the cell, and it is still "A" --> setValue overwrites "A" with "B"

I know the script is running as it is expected to, no problem at all. I just want to learn why setFormula "knows" from the previous run that the cell was already written and thus it does not update the value.

This is a short script to explain the question. Url value is set on the sheet and it is https://engaliao.com/html.htm as you can see in this picture


function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name: "Formula", functionName: "runFormula"},
    {name: "SetValue", functionName: "runSet"}

  ];
  sheet.addMenu("Menu", entries);
}

function runFormula() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
    'hoja'
  );
  activeSheet.getRange(2, 2).setFormula('=RunCheck' + '(A2)');
}

function runSet() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
    'hoja'
  );
  var workingCell = activeSheet.getRange(3, 1).getValue();
  activeSheet.getRange(3, 2, 1, 1).setValue(RunCheck(workingCell));
}

function RunCheck(Url) {
  var array = [];
  var response = UrlFetchApp.fetch(Url);
  if (response.getResponseCode() == 200) {
    var content = JSON.parse(response.getContentText());
    var score = content['loadingExperience']['id'];
  }
  array.push([score]);
  return array;
}

New simplified script that does not need an external fetch to explain my question:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name: "Formula", functionName: "runFormula"},
    {name: "SetValue", functionName: "runSet"}

  ];
  sheet.addMenu("Menu", entries);
}



function runFormula() {
var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("hoja");


    activeSheet.getRange(2, 2).setFormula("=RunCheck" + "(A2)");

     }   


function runSet() {
var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("hoja");

          var workingCell = activeSheet.getRange(3, 1).getValue();

          activeSheet.getRange(3,2,1,1).setValue(RunCheck(workingCell));


}



function RunCheck(text) {

    var array = [];

   var number = (Math.random())



    array.push([text + " " + number]);

    return array;
}

Do you get the same behaviour if you add "SpreadsheetApp.flush();" to the end of runSet()? My guess is that the formula is updated but the function hasn't been called and therefore there has been no update. By the way, without knowing what you're putting in the cells on the sheet "hoja", it's hard to test the code. — Paul, Jun 06 '20 at 13:50
Post edited to show the actual sheet. `SpreadsheetApp.flush();` did not make any change to the behaviour. This is a sample script made to explain my question. The real thing is that `Url` target html file does not contain the same information always, it is changing, and I have a time trigger to run the functions each hour. I would to understand why `setFormula` does not go to `runCheck` to update the values, and cell values remain from first trigger run, but `setValues` does update the values. In order to test the code, I should update the file between your first and second run — Lolo, Jun 06 '20 at 15:33
You should really be using setValue() for a single cell. setValues() is intended to write a 2 dimensional array. — Cooper, Jun 06 '20 at 16:22
Thank you @Cooper you are right, that's because the script comes from a larger one where I set more than 1 value. Anyway, post is edited. — Lolo, Jun 06 '20 at 16:52
I've actually used setValue() to write formulas with no problems. — Cooper, Jun 06 '20 at 16:53
Yes, of course, setValue and setFormula work with no problems, both of them. That's no my question. My question is why setFormula does not update the value in a second run after Url data has changed, but setValue does update it. I know this is not an "issue" or faulty code, I am just trying to understand the reason. — Lolo, Jun 06 '20 at 17:09
B2 and B3 are empty in your pic. What do you mean by "A" and "B"? — TheMaster, Jun 06 '20 at 19:46
`URLFetchApp` gets a value from an URL. The value is "A" before first triggered run, and then changes to "B" before the second triggered run. `setValue` and `setFormula` set differentes values in this second run. Main post edited to make it clear. Values shown are for first run. — Lolo, Jun 06 '20 at 20:26
Main post edited with new and self-explanatory script of what I mean about setFormula updating or not. — Lolo, Jun 07 '20 at 11:18

Cooper · Answer 1 · 2020-06-07T01:14:16.693

5

Your claim is not reproducible

I used these functions to test you statement that setForumula won't change the formula if it detects something in the cell and I found that it will update the formula whether it has been changed by setFormula or setValue and both in fact will update the formula.

function setMyValue() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet40');
  const v=sh.getActiveCell().getFormula();
  sh.getRange('A1').setValue(v);
  ss.toast(sh.getRange('A1').getFormula());
}

function setMyFormula() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet40');
  const v=sh.getActiveCell().getFormula();
  sh.getRange('A1').setFormula(v);
  ss.toast(sh.getRange('A1').getFormula());
}

So how about sharing you menu driven functions. Perhaps that is where the problem lies.

Animation:

edited Jun 07 '20 at 01:14

answered Jun 07 '20 at 00:56

Cooper

59,616
6
23
54

Thank you @cooper for your example, it was helpful. I think my scenario is not the same as yours. My formula is a custom formula and its value depends on running the script. For a first run, =runcheck(B2) runs the custom formula and retrieves external data. But in second run, =runcheck(B2) remains the same without running the custom formula again. I think I found it is not script related but inherit to Google Sheets app. When a formula is set to a cell, it does not update value if set arguments are the same. See https://i.imgur.com/yOMcLKj.gif .BTW, main post edited with my custom menu code. – Lolo Jun 07 '20 at 10:19
Main post edited with new and self-explanatory script of what I mean about setFormula updating or not. – Lolo Jun 07 '20 at 11:17
Is no one else impressed by the fact that Cooper threw together a sidebar specifically to show the behaviour? +1 for going the extra mile for clarity's sake! Also, on a selfish note, if you could share the code for the sidebar, Cooper, that would be interesting to see (although not directly relevant to the question at hand). – Paul Jun 07 '20 at 16:24
Actually, I didn't throw that sidebar together. I use the same spreadsheet for dealing with SO questions and it has a lot special features in it that I find helpful and one of them is that I can select the names of all of the functions in files ag1...ag3 from a dropdown list and dispatch to those into run(1...n) functions so that I can have a single button click to test new SO answer functions in a hurry. And yeh, I also think about it terms of gif videos because I tend to minimize the amount text in my answers. If I think I can get away with it my minimum text is `Try this:` – Cooper Jun 07 '20 at 16:38

Trying to understand setFormula vs setValue behaviour

1 Answers1