Automatic data record in Google Sheets

Question

I receive the data on the P/E multiple in Google Sheets via GOOGLEFINANCE in the following manner:

=GOOGLEFINANCE("GOOGL","pe")

How to write script in Google Sheets that would record the P/E values (or better the values of a particular cell) every day as at, say, 21:00?

Answer 1

Use Google Apps Script.

You want to write a function that uses the Spreadsheet API to get the value of a given Range in a given Sheet, and then append that value in a new row on a different sheet.

You also want to set up a time-based trigger to execute that function.

As an example, for a bound script with only one worksheet:

function copyA1toA2onSameSheet() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    sheet.getRange("A2").setValue(
        sheet.getRange("A1").getValue()
    );
}

Note that getActiveSheet will always return the first sheet from a time-based trigger, as there is no UI instance. If you need a different sheet, consider getSheetByName, or using getSheets().