Google Script: Updating a Single Cell with an IMPORTDATA Formula

0
votes

I have a single IMPORTDATA formula in Cell A1 of a Google Sheet named "test-r" that I would like to be able to update the cell on a specific interval -- anywhere from seconds to hours.

After a good amount of research, I landed upon this suggestion in a previous post, but I am not having much success with it.

Here is how I modified the script in that post for my IMPORTDATA formula in Cell A1 of my sheet/tab name of "test-r"

function forceEval(sheetName, Row, Col){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("test-r");
  var orig = sheet.getRange(1,1).getFormula(); 
  var temp = orig.replace("=", "?");
  sheet.getRange(row,col).setFormula(temp); 
  SpreadsheetApp.flush();
  sheet.getRange(row,col).setFormula(orig); 
}

function onEdit(e){
    forceEval("test-r", 1, 1)
}

This has got to be 'operator error' on my part and I am new to this.

At the same time, I don't know if there is a more simplistic script for accomplishing my goal.

Any assistance would be appreciated.

1
google-apps-scriptgoogle-sheets

1 Answers

0
votes
  • You want to update the function of cell "A1" every specific interval time.

If my understanding is correct, how about this modification?

Modification points:

  • In your script, OnEdit event trigger is used. In this case, when the cell is edited, the trigger is fired.
    • I think that this might not be suitable for your situation.
    • How about using the time-driven trigger?
  • There are some misspellings in your script. By this, variables are not used, and several error occur.

When above points are reflected to your script, it becomes as follows.

Modified script 1:

function forceEval(){ // Modified
  var sheetName = "test-r";
  var cell = "A1";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName); // Modified
  var orig = sheet.getRange(cell).getFormula(); // Modified
  var temp = orig.replace("=", "?");
  sheet.getRange(cell).setFormula(temp); // Modified
  SpreadsheetApp.flush();
  sheet.getRange(cell).setFormula(orig); // Modified
}
  • After you modified the script, in order to execute the function of forceEval() every specific interval time, please install the time-driven trigger.
  • In this script, #NAME? is displayed for an instant. If you don't want to do this, how about the following sample script?

Modified script 2:

function forceEval(){
  var sheetName = "test-r";
  var cell = "A1";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getSheetByName(sheetName).getRange(cell);
  var formula = range.getFormula();
  range.clearContent();
  SpreadsheetApp.flush();
  range.setFormula(formula);
}

Install time-driven trigger:

In order to install the time-driven trigger, you can manually install it.

Also you can install it using the script. The following script installs the time-driven trigger for the function of forceEval. In this sample script, when setTrigger() is run, the function of forceEval() is run every 10 minutes. By this, the formula of the cell "A1" is updated every 10 minutes.

function setTrigger() {
  var minutes = 10; // In this case, as a sample value, it's 10 minutes.
  var functionName = "forceEval";

  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() == functionName && triggers[i].getTriggerSource() == ScriptApp.TriggerSource.CLOCK) {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
  ScriptApp.newTrigger(functionName).timeBased().everyMinutes(minutes).create();
}

Reference:

If I misunderstood your question and this was not the direction you want, I apologize.