0

I have a custom function using DeveloperMetadata from other sheets in the workbook. The issue I have is that if I e.g. delete one of previous sheets (thus removing that metadata) the cell value doesn't change. The only trigger which recalculates this function is to make a simple whitespace change in the Script Editor and save the file.

Is there a method in Google Sheets to do this refresh automatically?

function get_prev_days(rest_key) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var sheet_index = SpreadsheetApp.getActiveSheet().getIndex();
  var value = 0;
  for (var i = 0; i < sheet_index; i++) {
    var meta = sheets[i].getDeveloperMetadata();
    for (var j = 0; j < meta.length; j++) {
      if (rest_key == meta[j].getKey()) {
        value += Number(meta[j].getValue());
      }
    }
  }
  return value;
}
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • hi Łukasz, could you explain a little bit better your question? Giving a working example or data from the sheets and more specific problems, as I don't really understand what's your issue. – JV Lobo Feb 23 '19 at 00:19
  • Hi, in google sheets you can create custom function. If You put "= get_prev_days("key_string")" You will get sum over all PREVIOUS spreadshitts ( all with index < sheet_index). Example. I have 3 sheets with metadata Sheet1 ("meta" =1), sheet2 ("meta" =2), Sheet3( "meta" = 3). when for example in Sheet2 in cell A2 ill put "=get_prev_days( "meta") i should get in A2 = 1 + 2 +3. Problem is that if i remove Sheet 1, value in sheet3!A2 wont change even if now its 2+3. To force sheet to recaut it i have to simulate that something changed in shhet script. Im looking for another way. – Łukasz Andrzejewski Feb 23 '19 at 01:01
  • Did you read the restrictions on custom functions, clearly stated in documentation? The output is supposed to be deterministic based on the function parameters. If it is not, that is your error. – tehhowch Feb 23 '19 at 15:31
  • Required reading: https://developers.google.com/apps-script/guides/sheets/functions#guidelines_for_custom_functions – tehhowch Feb 23 '19 at 15:40
  • Possible duplicate of [Refresh data retrieved by a custom function in Google Sheet](https://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-sheet) – tehhowch Feb 23 '19 at 15:47
  • @tehhowch parameter is deterministic, i put key value, every sheet has its key value. I don't get error i just get old value, and to trigger my function to recount sheets i have to for example save script again (without any code changes) Its not this case, i found this issue before asking question and none of solutions from there works here, im trying with ScriptApp.. – Łukasz Andrzejewski Feb 23 '19 at 18:49

0 Answers0