Google-sheets – Find changes in imported data daily

The following formula would yield the most elegant presentation.

Formula

=QUERY(DATA!A:C, "SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A PIVOT B")

Screenshot

This is a good alternative, but formula wise a bit different.

Formula

=QUERY(DATA!A:C, "SELECT A, B, SUM(C) WHERE A IS NOT NULL GROUP BY A, B   
   LABEL SUM(C) 'Costs'")

Screenshot

Example

I've added the results into your dummy sheet.

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.