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.
Generating a direct download link, as described in this article seems to work fine with the =IMPORTDATA function.
To summarize, you take the SITE_ID string from the sharing link you have:
https://drive.google.com/file/d/FILE_ID/edit?usp=sharing
Using this, you can generate the direct download link for use with the =IMPORTDATA function:
https://drive.google.com/uc?export=download&id=FILE_ID
Best Answer
You could Google Apps Script, the Google Sheets API or G Suite Document Editor add-on to automate tasks on Google Sheets.
To ask for an add-on recommendation please post a question on https://softwarerecs.stackexchange.com
To use Google Apps Script, please start reading https://developers.google.com/apps-script/guides/sheets
To learn about the Google Sheets API, please reas https://developers.google.com/sheets.
It's worth to note that Google Apps Script or an add-on can't access a local file so first you have to someway upload that file to the cloud (Google Drive, or something like that).