Как обновить значение ячейки листа в скрипте Google Apps

Я экспериментирую с Blockspring, который предоставляет надстройку Google Sheets, которая может, например, запускать функцию, которая возвращает данные из веб-службы, например

=BLOCKSPRING("get-stock-current-stats", "ticker", "MSFT")

Я хочу обновить данные ячейки, но не вижу в документах вызова "обновить".

function onOpen() {
  createTimeDrivenTriggers()
}

function createTimeDrivenTriggers() {
  // Trigger every minute
  ScriptApp.newTrigger('myFunction')
      .timeBased()
      .everyMinutes(1)
      .create();

}

function myFunction() {
  Logger.log('I ran'); // can see this in the logs
  SpreadsheetApp.getActiveSheet().getRange('B4').getValue() //presumably returns a value to the script

}
Источник

2 ответа

Решение

Использовать flush() метод:

Документация Google

SpreadsheetApp.flush();

Цитата из документации:

Применяет все ожидающие изменения таблицы

Если в таблицу не было внесено никаких изменений, но вы хотите принудительно пересчитать формулы, вам нужно будет внести изменения, а затем использовать SpreadsheetApp.flush();

Например, вы можете получить значение из ячейки A1, а затем установить то же значение в ячейку A1. Таким образом, нет никакой возможности потерять данные, потому что вы получаете и устанавливаете одно и то же значение. Изменение позволяет. SpreadsheetApp.flush(); пересчитать все формулы.

Источник

Вместо того, чтобы использовать flush, вы можете указать электронной таблице, от каких ячеек зависит функция, передав их в формулу в конце ее ожидаемых параметров. Можно передать больше параметров, чем будет использовать функция. Таким образом, электронная таблица будет предполагать, что значение, возвращаемое функцией, может быть другим, если входные данные разные, и об этом позаботится обычный процесс обновления.

Если вы хотите, чтобы он переоценивал изменение любой ячейки, просто передайте диапазон, включающий все ячейки.

Например:

=MyFunction("this", "that", A1:Z10000)

Если вы потратите время на передачу именно тех ячеек, от которых функция логически зависит, это будет более эффективно, поскольку не будет повторной оценки, когда в этом нет необходимости.

=MyFunction("this", "that", A10, C5, G6:H9 )

Если это действительно не зависит от какой-либо ячейки, но вы хотите оценить функцию вручную, по запросу создайте ячейку, которая увеличивает свое скрытое значение, когда пользователь нажимает на нее (см. Кнопки), а затем передайте адрес этой ячейки в функцию.

=MyFunction("this", "that", A10 )
// make A10 change its value on a button click
Источник

Обновить всю таблицу Это медленно, но работает!

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var rangeData = sheet.getDataRange();
var lastColumn = 38;
var lastRow = 17;
var searchRange = sheet.getRange(2,2, lastRow-1, lastColumn-1);

function forceRefresh() {
  //Loop through each column and each row in the sheet.
  for(i = 1; i < lastColumn; i++){
    for (j = 1; j < lastRow ; j++){
      var cell = searchRange.getCell(j,i);
      var formula = cell.getFormula();
      var tempFormula = formula.replace("=", "?");
      cell.setFormula(tempFormula);
      SpreadsheetApp.flush();
      cell.setFormula(formula);
    };
  };
};

forceRefresh(); //call
Источник

Размещение полезного сниппета на случай, если вам нужно принудительно пересчитать все формулы. Это значительно эффективнее, чем другие примеры, размещенные здесь.

function forceRefreshSheetFormulas(sheetName) {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = activeSpreadsheet.getSheetByName(sheetName);
  var range = sheet.getDataRange();
  var numCols = range.getNumColumns();
  var numRows = range.getNumRows();
  var rowOffset = range.getRow();
  var colOffset = range.getColumn();

  // Change formulas then change them back to refresh it
  var originalFormulas = range.getFormulas();

  //Loop through each column and each row in the sheet
  //`row` and `col` are relative to the range, not the sheet
  for (row = 0; row < numRows ; row++){
    for(col = 0; col < numCols; col++){
      if (originalFormulas[row][col] != "") {
        range.getCell(row+rowOffset, col+colOffset).setFormula("");
      }
    };
  };
  SpreadsheetApp.flush();
  for (row = 0; row < numRows ; row++){
    for(col = 0; col < numCols; col++){
      if (originalFormulas[row][col] != "") {
        range.getCell(row+rowOffset, col+colOffset).setFormula(originalFormulas[row][col]);
      }
    };
  };
  SpreadsheetApp.flush();
};

Есть несколько хитростей: - Установка формулы ячейки на ""очищает его, поэтому мы этого избегаем. - Нам нужно изменить формулу, промыть, изменить обратно и снова промыть.

Источник

Пример на основе @Sandy_Good ответа выше. Добавление, поскольку мне пришлось немного потрудиться, чтобы понять, как использовать это для автоматической оценки формулы ячейки при любом изменении листа. Этот код работает медленно, но я поделюсь им в качестве общего примера в надежде, что это поможет, YMMV.

/* Force evaluation of custom formula on sheet change.
* Temporarily changes formula text.
* @param sheetName: String Needs to be present in the formula
* @param row: Int
* @param col: Int 
*/

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

Теперь вызовите его с помощью триггера onEdit.

function onEdit(e){
    forceEval("MySheet", 1, 1)
}

Это временно переписает формулу в ячейке 1,1, заменив "=" на "?", Затем flush(), а затем вернет ее обратно, что приведет к оценке после любого редактирования на листе.

Пользовательская формула в ячейке может выглядеть примерно так: =SUMSTATUS("MySheet","InProgress")

Должно работать для: =BLOCKSPRING("get-stock-current-stats", "ticker", "MSFT")

Источник

Выберите ячейку с формулой или несколько ячеек с формулами. В меню Google выберите:Add-ons > Blockspring > Refresh Selected Cells

надеюсь, это поможет

Источник
Другие вопросы по тегам