Google-sheets – IF and IFS not working properly

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.

the query only grabs the headers.

No, it grabs some rows, too. Scroll down... way, way down. There they are. Here's why you are hitting this.

1. Some of your columns are completely empty. These are interpreted as string by default. Hence, is not null is always true for these, and you get the whole table.
2. Since you order by B (ascending by default), the huge number of empty rows ends up on top, and leads you to conclude that the query returns nothing.

Unfortunately, there seem to be no way to declare the type of an empty column. This, combined with the "no nulls in string column" behavior, leads to people being driven crazy by not null.

To keep your sanity, either

• make sure every column has at least one nonempty cell, with some value of the type you expect in that column.
• or, use the less powerful but more straightforward filter command when it suffices for the task. Note that the output of filter can be fed into a query, to combine the best features of both.