7

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!

Rubén
  • 34,714
  • 9
  • 70
  • 166
marko
  • 73
  • 1
  • 4
  • 1
    nope, http://stackoverflow.com/a/25473705/1480465. you have to re-create them. – Bryan P Oct 10 '14 at 07:48
  • possible duplicate of [Use the Spreadsheet standard functions in Scrips?](http://stackoverflow.com/questions/25472985/use-the-spreadsheet-standard-functions-in-scrips) – KyleMit Mar 13 '15 at 18:48

4 Answers4

1

You can try this:

var trick = SpreadsheetApp.getActiveSheet().getRange('D2').setValue('=GOOGLEFINANCE("GOOG")').getValue();
Dino
  • 7,779
  • 12
  • 46
  • 85
GuestGuy
  • 11
  • 1
0

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.

Serge insas
  • 45,904
  • 7
  • 105
  • 131
0

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

tothemax
  • 11
-1

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.

Zae Vilches
  • 7
  • 2