Google Scripts automatically add date to column in Google Sheets

0
votes

I am trying to use a Google script that retrieves 2 securities fields from GOOGLEFINANCE and saves the output to a Google Sheet file. I need the script to also add the datetime to the first column of the Sheet.

I have created a basic Google Sheet with 3 columns:

  1. A is formatted to DateTime. It has column name date in row 1 and is empty in rows 2 onwards
  2. C has the column name price in row 1 and is empty in rows 2 onwards
  3. D has the column name pe in row 1 and is empty in rows 2 onwards

Here is my function:

function myStocks() {

  var sh = SpreadsheetApp.getActiveSpreadsheet();

  sh.insertRowAfter(1);
  sh.getRange("A2").setValue(new Date());

  sh.getRange("C2").setFormula('=GOOGLEFINANCE("GOOG", "price")');
  sh.getRange("D2").setFormula('=GOOGLEFINANCE("GOOG", "pe")');
}

Here is the output:

Date                    price   pe
12/10/2017 22:44:31     1037.05 34.55
12/10/2017 22:43:24     1037.05 34.55

The output of columns C and D is correct. The output of column A is wrong. Every time I run the function, each new row is added ABOVE the last row:

  1. The first time I ran the function was at 12/10/2017 22:43:24 and it added that row first.
  2. The second time I ran the function was 12/10/2017 22:44:31 BUT it added that row ABOVE the last row in the sheet - I wanted it to add the new row BELOW the last row.

Is there a way to auto fill the datetime downwards in a single column in GoogleSheets, using a script function?

1
datetimegoogle-apps-scriptgoogle-sheetsgoogle-finance

1 Answers

2
votes

How about the following modifications?

Modification points :

  • sh.insertRowAfter(1) means that a row is inserted between 1 row and 2 row.
    • In your situation, you can retrieve the last row using getLastRow().
  • getRange("A2").setValue(), getRange("C2").setFormula() and getRange("D2").setFormula() mean that the values are imported to "A2", "C2" and "D2", respectively.
    • By this, the values are always imported to 2 row.
  • When you want to import several values and formulas to sheet, you can use setValues() and setFormulas().

The script which was reflected above points is as follows.

Modified script :

function myStocks() {
  var sh = SpreadsheetApp.getActiveSheet();
  var lastrow = sh.getLastRow() + 1; // This means a next row of last row.
  sh.getRange(lastrow, 1).setValue(new Date());
  var formulas = [['=GOOGLEFINANCE("GOOG", "price")', '=GOOGLEFINANCE("GOOG", "pe")']];
  sh.getRange(lastrow, 3, 1, 2).setFormulas(formulas);
}

Note :

  • In your script, date and 2 formulas are imported, simultaneously. The modified script works the same to this.

References :

If I misunderstand your question, please tell me. I would like to modify.