0

I have a simple function to get some cell value

function getValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[4];

  var range = sheet.getRange("C2:C4");

  var cell = range.getCell(1, 1); // "C2"
  if (cell.isBlank()) {
      return 'error'
  } else {
      return cell.getValue()  
  }
}

But when I change data in C2, cell, which contains =getValue() function does not refresh itself instantly. Only if I run script again and get back to sheet. Is it possible to speed this process up? Any code for this? Thanks.

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • I believe when you manually enter data into a cell with a formula that you overwrite the formula. I'm not sure what the point of this function is but if you want to get it work you might consider an `onEdit()` function. – Cooper Nov 06 '17 at 23:19
  • 2
    Is there a reason to use a script custom function `=getValue()` instead of something like `=IF(ISBLANK(C2),"error",C2)`? – random-parts Nov 06 '17 at 23:30
  • Related: [Custom functions and recalculation](https://stackoverflow.com/q/25163243/1595451) – Rubén Nov 08 '17 at 00:51
  • Was my answer useful for you? If you have problems for my answer yet, feel free to tell me. I would like to study to solve your problems. – Tanaike Nov 14 '17 at 22:13

1 Answers1

0

If you have to use the custom functions for this situation, how about this workaround? I don't know whether this is the best way for you. Please think of this as one of several answers.

The flow of script is as follows.

Flow :

  1. Retrieve all values and formulas on the sheet.
  2. Remove values of cells which have formulas.
  3. Reflect values to the sheet using SpreadsheetApp.flush().
  4. Import formulas to the removed cells.

By onEdit(), when you edit the cell, this sample script is launched.

Sample script :

function onEdit(e) {
  var range = e.source.getDataRange();
  var data = range.getValues();
  var formulas = range.getFormulas();
  var values = data.map(function(e){return e.slice()});
  for (var i in formulas) {
    for (var j in formulas[i]) {
      if (formulas[i][j]) {
        data[i][j] = formulas[i][j];
        values[i][j] = "";
      }
    }
  }
  range.setValues(values);
  SpreadsheetApp.flush();
  range.setValues(data);
}

Note :

  • In this situation which imports a value at "C2" to the cell at =getValue(), the refresh speed is slower than that of @random-parts's method.
  • To use onEdit() is also proposed from @Cooper.

If this was not useful for you, I'm sorry.

Tanaike
  • 181,128
  • 11
  • 97
  • 165