0

The following Apps Script does not retrieve the calculated value for a formula of =max(index(googlefinance(A2,"close","1/2/2018",today()),0,2)). The logger shows #REF!.

Anything wrong? I'm guessing it's related to the index() but no idea how to proceed further.

1) Opening the sheet in a browser would show a calculated value at cell B2. The cell calculates the stock's YTD high price.

          column_A   column_B
row_1     symbol    YTD High                                                                
row_2     SPY      =max(index(googlefinance(A2,"close","1/2/2018",today()),0,2))

2) script:

function getStock(location) {

  var sheet = SpreadsheetApp.getActiveSheet();

  var activeRange = sheet.getDataRange();
  var values = activeRange.getDisplayValues();
  var formula = activeRange.getFormulas();
  Logger.log(values[0]);
  Logger.log(values[1]);
  Logger.log(formula[1]);
}

3) logger output:

[18-05-22 14:48:10:431 PDT] [symbol, YTD High]
[18-05-22 14:48:10:432 PDT] [SPY, #REF!]
[18-05-22 14:48:10:433 PDT] [, 
=max(index(googlefinance(A2,"close","1/2/2018",today()),0,2))]
Community
  • 1
  • 1
wilsonh
  • 1
  • Unfortunately, you cannot retrieve the values. The detail information is https://gsuiteupdates.googleblog.com/2016/09/historical-googlefinance-data-no-longer.html – Tanaike May 22 '18 at 22:36
  • @Tanaike That explains. I saw a post saying you need to keep Sheet open to use googlefinance(), but didn't realize that the API shuts its door on Apps Script. Thanks. – wilsonh May 22 '18 at 23:13
  • If you have to retrieve the value no matter what, there is a workaround. When the spreadsheet is converted to an image, the values are included in the image. Using this, convert the spreadsheet to an image and convert the text data using ocr, and retrieve the values. This can be run by a script. But the parse of values by script strongly depends on the format of spreadsheet. – Tanaike May 23 '18 at 02:59
  • @Tanaike that's creative lol thanks – wilsonh May 23 '18 at 03:49

1 Answers1

1

Workaround using Range.copyValuesToRange():

sheet.getRange(row, col).copyValuesToRange(tempSheet, 1, 1, 1, 1);
var value = tempSheet.getRange(1, 1).getValue();
tempSheet.getRange(1, 1).setValue("");

Works for me.

Valera Leontyev
  • 1,191
  • 6
  • 14