Can I use Google Spreadsheet features in Google Script app?

I just opened Google App Scripts and I am already stumped on something already ...

I am trying to write a script for Google Spreadsheet that finds specific historical stock prices. I found that the FinanceApp service in google app scripts is deprecated and seemingly replaced by the GOOGLEFINANCE () function in google spreadsheets. However, it returns an array when I only need one cell and the array is ruinous.

So, I would like to write a short script that calls the spreadsheet function GOOGLEFINANCE (), and only finds 1 piece of information that I need from the array returned by GOOGLEFINANCE (). However, I cannot find a way to access the spreadsheet functions (SUM, VLOOKUP, GOOGLEFINANCE, etc.) in the script.

Is there a way to access these functions in a script? Or maybe there is a new service that replaces the legacy FinanceApp service?

Thanks a lot for any help!


source to share

3 answers

Built-in spreadsheet functions are not supported in Google Apps Script.

You can end up with a somewhat cumbersome workaround by reading the value of the cell where you write the formula (using a script to write and read), but this will be less practical and / or fast.



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

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); 



All formulas that you write in this function are treated as strings, which must have an "or" inside .setFormula ().



You can try this:

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




All Articles