google-apps-script - 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;
}

标签： google-apps-scriptgoogle-sheets

Answer 1

您的声明不可重现

我使用这些函数来测试您的声明，即如果 setForumula 在单元格中检测到某些内容，它不会更改公式，并且我发现无论它是否已被 setFormula 或 setValue 更改，它都会更新公式，实际上两者都会更新公式。

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());
}

那么如何分享您的菜单驱动功能。也许这就是问题所在。

动画：