Clever Geek Handbook

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!

+3


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.

+1


source


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

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


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

0


source


You can try this:

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


source






More articles:


All Articles