Google-sheets – Automatically add rows daily and append previous day’s data

google sheets

I currently have a small database that gets data dumped into it manually every day that looks like this. Each day the data below will be dumped (just with updated date in column A)

pic 1 - initial database dump

On another sheet I am trying to create a daily snapshot of the total cash that is in the machines (E:E + F:F + G1) and the total volume (C:C) on a specific day. In this case it would be for 7/27/15. Hopefully looking something like this:

pic 2 - sheet with formulas layered on database dump

What I would like is for each day that I add in new data to the original sheet that the sheet with the formulas appends the previous date and adds in a row with todays formulas/data. Like this:

pic 3

Hopefully someone out there understands what I am trying to accomplish. I understand formulas quite well with sheets I just tried to word and present my problem as clearly as possible as no formulas that I've tried have worked.

Best Answer

Without a script

First, fill column A of the second sheet with 

=IF(TODAY()-ROW()+2>=DATE(2015,1,1), TODAY()-ROW()+2, "")

where DATE(...) is the date that your records begin. This creates a reverse-chronological list of dates, beginning with today. It will be automatically updated as TODAY() changes. As any non-script solution, this approach is limited by the fact that formulas can not add rows to a sheet. So you should give the second sheet plenty of rows to begin with.

In column C, put formulas like 

=IF(A2="", "", SUMIF(Sheet1!A:A, "="&A2, Sheet1!C:C))

totaling the C amounts for a given date, if there is a date.

In column B, put something similar:

=IF(A2="", "",  SUMIF(Sheet1!A:A, "="&A2, Sheet1!F:G) + VLOOKUP(A2, Sheet1!A:G, 7, FALSE))

where VLOOKUP takes one value from column G.

A disadvantage of the above is that the calculations will be slow because all the data on Sheet2 are recalculated every day.

With a script

A script can use the same formulas as given above: no need to replicate the logic. Additional benefits:

  • rows inserted automatically
  • possible to freeze archived values in Sheet2, so that if someone goes back to fudge the books in Sheet1, the record in Sheet2 will stay.

Here is such a script: 

function daily() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Sheet2");
  sh.insertRowAfter(1);
  sh.getRange("A2").setFormula('=A3+1');
  sh.getRange("B2").setFormula('=SUMIF(Sheet1!A:A, "="&A2, Sheet1!F:G) + VLOOKUP(A2, Sheet1!A:G, 7, FALSE)');
  sh.getRange("C2").setFormula('=SUMIF(Sheet1!A:A, "="&A2, Sheet1!C:C)');
  var freeze = sh.getRange("A3:C3");
  freeze.copyTo(freeze,{contentsOnly:true});  
}

The code is mostly self-explanatory: a row is added and filled with formulas. The two lines with "freeze" are optional: they replace the formulas in yesterday's row with the calculated values. If you don't want this to happen, remove them.

Column A should be formatted as "date" so that it displays correctly. You will also need to put a date in A2 to get the process started; subsequent dates will appear automatically.

To have the script run daily, add a trigger from the Script Editor: it's under "Resources > Current project's triggers". You can pick the hour of day when the script should run.

Related Topic