Possible to use Google Spreadsheet functions in Google App Script?

6
votes

I just discovered Google App Scripts, and I'm stumped on something already...

I am trying to write a script for a Google Spreadsheet which finds certain historical stock prices. I found that the FinanceApp service within Google App Scripts has been deprecated, and seemingly replaced by the GOOGLEFINANCE() function within Google Spreadsheets. However, it returns an array, when I need only a single cell, and the array is mucking up the works.

So I'd like to write a short script that calls the GOOGLEFINANCE() spreadsheet function, and finds just the 1 piece of info I need from the array which is returned by GOOGLEFINANCE(). However, I cannot find a way to access Spreadsheet Functions (SUM, VLOOKUP, GOOGLEFINANCE, etc) within a script.

Is there a way to access these functions in a script? Or perhaps, is there a new service which replaces the deprecated FinanceApp service?

Many thanks for any assistance!

4
google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-finance
nope, stackoverflow.com/a/25473705/1480465. you have to re-create them. – Bryan P
possible duplicate of Use the Spreadsheet standard functions in Scrips? – KyleMit

4 Answers

0
votes

Native Spreadsheet functions are not supported in Google Apps Script.

You could eventually use a somewhat cumbersome workaround by reading the value of a cell in which you write a formula (using script in both write and read) but this will be less than practical and / or fast.

1
votes

You can try this:

var trick = SpreadsheetApp.getActiveSheet().getRange('D2').setValue('=GOOGLEFINANCE("GOOG")').getValue();
-1
votes

There is one possible way, with the .setFormula(). This function behave like .setValue() and can be used the following way: 

var ss = SpreadsheetApp.getActiveSpreadsheet();
var mySheet = ss.getSheets()[0]

//Code Below selects the first cell in range column A and B
var thisCell = mySheet.getRange('A:B').getCell(1,1); 

thisCell.setFormula('=SUM(A2:A4)');

All formulas you write in this function are treated as strings must have ' or " within the .setFormula() input.

-1
votes

You might try the INDEX function combined with GOOGLEFINANCE-

For reference,

=GOOGLEFINANCE("MSFT", "PRICE", "01/01/21")

Returns the array:

    Date    Close
    1/4/2021    217.69

One can add the INDEX function to pick out specific elements from the array using the row,column coordinates of the array.

=INDEX(GOOGLEFINANCE("MSFT", "PRICE", "01/01/21"),2,2)

This returns just the data in row 2, column 2 - 217.69