4

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.

3

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.

  • Cant you make that work by using an ARRAYFORMULA or use QUERY to contruct the other sheet? – Jacob Jan Tuinstra Jul 29 '15 at 8:44
  • So I completely understand SUMIF(s) and I have those formulas down to add everything up correctly for a given day. The issue I'm having is that using something like ARRAYFORMULA does not populate the rest of the cells. Also, I need a new row to be inserted automatically everyday with the same formulas and to have the previous days data/formulas pushed down to the row below. That way every single day the sheet gets updated automatically with new data and logs the old. – Cailen Sullivan Jul 29 '15 at 18:06
  • @JacobJanTuinstra This was a somewhat tentative answer, which succeeded at clarifying what needs to be done. In particular, newest data should go on top, which I suppose can still be done with some queries (rebuilding the second sheet every day), but probably easier with a script. – user79865 Jul 29 '15 at 19:52
  • @CailenSullivan I have rewritten the answer. – user79865 Jul 30 '15 at 16:34
  • @NormalHuman Thank you for that! I'm close, but still not quite there. Would you be willing to help me out via direct contact (email, screenshare, etc)? Not sure if I can convey the issue I'm running into on this thread. Let me know if this is possible :) – Cailen Sullivan Jul 31 '15 at 0:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.