Google Spreadsheets Script - Sum By Date

Google Spreadsheets Script - Sum By Date

// Sum all rows by day in a 2 column Google Spreadsheet containing a date and a number : I use it with a trigger that runs every day
// To free space in a constantly updated spreadsheet.
// Author : Vincent Hsu -> twitter.com/suisseo -> http://www.suisseo.ch
// Language : Javascript
// Usage : aggregateDailySum(tabname,days)
// where tabname is the a string containing the name of the tab to update and days a number : if days is for example 1 the script
// will look look for all rows containing yesterday's date in the first column and sum the numbers in the second column, then it will 
// delete the rows and insert the summed up row. Dates must be sorted in order for the script to work.


//Aggregate the data  
function aggregateDailySum(tabname, days) {
    var SPREADSHEET_URL = "https://docs.google.com/spreadsheet/ccc?key=YOURSPREADSHEETKEY#gid=0";
    var ACCOUNT_NAME = tabname;
    var spreadsheet = getSpreadsheet(SPREADSHEET_URL);
    var sheet = spreadsheet.getSheetByName(ACCOUNT_NAME);
    var range = sheet.getDataRange();
    var values = range.getValues()
    var yesterday = new Date();
    yesterday.setDate(yesterday.getDate() - days);
    var byest = yesterday;
    yesterday = yesterday.toDateString(); 
    var total = 0;
    var startindex = "none";
    var numrows = 0;

    for (var i = 0; i < values.length; i++) {
        var row = "";
        var dataday = values[i][0].toDateString();
        if (dataday === yesterday) {
            if (startindex == "none") {
                startindex = i;
            }
            numrows++;
            total += values[i][1];
        }
    }

    if (startindex != "none") {
        sheet.deleteRows(startindex + 1, numrows);
    }
    sheet.insertRows(startindex + 1);
    sheet.getRange("A" + (startindex + 1)).setValue(byest);
    sheet.getRange("B" + (startindex + 1)).setValue(total);
}

// Get Spreadsheet Function 
function getSpreadsheet(spreadsheetUrl) {
    var matches = new RegExp('key=([^&#]*)').exec(spreadsheetUrl);
    if (!matches || !matches[1]) {
        throw 'Invalid spreadsheet URL: ' + spreadsheetUrl;
    }
    var spreadsheetId = matches[1];
    return SpreadsheetApp.openById(spreadsheetId);
}