Daily Donation Report

ddr.js

/*********************
*Function: addLocation
*Description: This function is called every time a form is submitted. Firstly, it adds the name of the store
*             to the location column. The Column is a hard-coded value, while the row is automatically populated.
*             Secondly, the entry is duplicated to a secondary spreadsheet called "Aggregate".
*@param {Event Object}: e
*@returns {Nothing}: N/A
**********************/
function addLocation(e) {
  
    var rowNum = e.range.getRow();
  
    //Add the location
    var sheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
    SpreadsheetApp.getActiveSheet().getRange('D' + rowNum).setValue(sheetName);
    
    //Make a copy to the Aggregate Sheet
    //This if-statement is necessary to prevent spurious onFormSubmit triggers to add junk data to the aggregate Sheet.
    //https://stackoverflow.com/questions/54834837/how-can-i-be-getting-multiple-unwanted-event-blocks-from-the-same-onformsubmit-t/54860085#54860085
    if(e.values && !e.values[1]){return;}
    var target_id = "REDACTED";
    var target_spreadsheet = SpreadsheetApp.openById(target_id);
    var target_sheet = target_spreadsheet.getSheetByName('Aggregate');
    var aggregate_row = [e.values[0], sheetName, e.values[1]];
    target_sheet.appendRow(aggregate_row);
  
}

/**************************
*Function: mailSheetAsExcel
*Description:  This function repackages a Google Sheet as an Excel file and delivers it to an array of recipients via email. 
*@param {N/A}: N/A
*@returns {Nothing}: N/A
**************************/
function mailSheetAsExcel() {

//    resetSheet("Aggregate");
//    concatAllSheetsForToday();

    var myHtmlBody = '<p style="font-size:30px; font-family: Brush Script MT; color: #D5AD6D;">';
    myHtmlBody += "Aloha all,";
    myHtmlBody += '</p>';
    myHtmlBody += '<p>';
    myHtmlBody += "Attached to this email is an excel spreadsheet containing all recorded donations for today, and in the table below is a summary.";
    myHtmlBody += '</p>';

    myHtmlBody += dailyReport();

    myHtmlBody += '<br>';
    myHtmlBody += "Please send an email to REDACTED@REDACTED.org if you spot any discrepancies.";
    myHtmlBody += '<p style="font-size:30px; font-family: Brush Script MT; color: #D5AD6D">';
    myHtmlBody += "Mahalo,";
    myHtmlBody += '<br>';
    myHtmlBody += 'IT';
    myHtmlBody += '</p>';

    try {

        var ss = SpreadsheetApp.getActive();
        var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx";
        var params = {
            method: "get",
            headers: { "Authorization": "Bearer " + ScriptApp.getOAuthToken() },
            muteHttpExceptions: true
        };
        var blob = UrlFetchApp.fetch(url, params).getBlob();
        var today = new Date();
        blob.setName(ss.getName() + " Snapshot " + today + ".xlsx");

        /*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!/
        /!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*/
        var emailAddress = "REDACTED@REDACTED.org";
        /*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!/
        /!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*/

        MailApp.sendEmail(emailAddress, "Donation Snapshot for " + today, "See the attached XLSX file and navigate to the Aggregate tab.", { htmlBody: myHtmlBody, attachments: [blob] });
    } catch (fail) {
        Logger.log(fail.toString());
    }
}

/**************
*Function: isSameDateString
*Description: Receives two date Strings and compares them. If they are evaluated to be equal then a value of true is returned.
*@param {String, String}: someDate, otherDate
*@returns {Boolean}: true or false
**************/
function isSameDateString(someDate, otherDate) {
    return someDate == otherDate;
}

/**************
*Function: yesterdaysDate
*Description: It's exactly what it sounds like.
*@param: N/A
*@returns {Date}: $yesterday
**************/
function yesterdaysDate(){
$today = new Date();
$yesterday = new Date($today);
$yesterday.setDate($today.getDate() - 1);
return $yesterday;
  }

/*************************
*Function: concatAllSheets
*Description: This function iterates through every row of every sheet in the workbook and
*             appends them to the 'Aggregate' sheet. This takes awhile and is a rather expensive
*             function because it's checking the dates of every row to make sure it's of today's
*             date before appending the row.
*@param {Nothing}: N/A
*@returns {Nothing}: N/A
*************************/
function concatAllSheetsForToday() {

    var includedSheet = getSheetNames(); //array of sheet names
    var ss = SpreadsheetApp.getActive();
    var allSheets = ss.getSheets();
    var mother = ss.getSheetByName("Aggregate");
    var today = new Date().toDateString();
    for (var i = 0; i < allSheets.length; i++) {
        var sheet = allSheets[i];
        if (includedSheet.indexOf(sheet.getName()) > -1) {
            var sheetValues = sheet.getDataRange().getValues();
            //index starts at 1 to skip header
            for (var j = 1; j < sheetValues.length; j++) {
                var row = sheetValues[j];
                var someDate = new Date(row[0]).toDateString();
                if (isSameDateString(today, someDate)) {
                    mother.appendRow(row);
                }
            }
        }
    }

}

/***********************
*Function: getSheetNames
*Description: Iterates through an entire spreadsheet and builds a String array of
*             the names of each sheet.
*@param {Nothing}: N/A
*@returns {String array}: out
***********************/
function getSheetNames() {

    var out = new Array();
    var ss = SpreadsheetApp.getActive();
    var allSheets = ss.getSheets();

    //index starts at 1 to skip the "Aggregate" sheet
    for (var i = 1; i < allSheets.length; i++) {
        var sheet = allSheets[i];
        out.push(sheet.getName());
    }
    return out;

}

/********************
*Function: resetSheet
*Description: Void function that completely erases a specific sheet.
*@param {String}: The Google Sheet with this name will be totally erased. !!!
*@returns {Nothing}: N/A
********************/

function resetSheet(name) {
    var ss = SpreadsheetApp.getActive();
    var sheet = ss.getSheetByName(name);
    var lastRow = SpreadsheetApp.getActiveSheet().getLastRow();
    var range = sheet.getRange(1, 1, lastRow, 5);
    range.clearContent();
}

/*********************
*Function: dailyReport
*Description: Builds a table of donation data. It iterates through every sheet and, if a row contains
*             a donation with today's date, counts how many items were donated and how many unique donators
*             there were. HTML tags and donation data are all built into a massive String that is returned. 
*@param {Nothing}: N/A
*@returns {String}: myHtmlBody
**********************/

function dailyReport() {

    var allSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    var myHtmlBody = '<table border="1" font-family: arial;">';
    myHtmlBody += '<thead>';
    var today = new Date().toDateString();
    myHtmlBody += '<th colspan="3">' + today + '</th>';
    myHtmlBody += '</thead>';
    myHtmlBody += '<tr>';
    myHtmlBody += '<td style="font-weight: bold;">' + "Location" + '</td>';
    myHtmlBody += '<td style="font-weight: bold;">' + "Total Donors" + '</td>';
    myHtmlBody += '<td style="font-weight: bold;">' + "Total Donations" + '</td>';
    myHtmlBody += '</tr>';

    //Index starts at 1 to skip aggregate sheet
    for (var i = 0; i < allSheets.length; i++) {

        var sheet = allSheets[i];
        var sheetName = sheet.getSheetName();
        var sheetValues = sheet.getDataRange().getValues();
        var totalDonors = 0;
        var totalDonations = 0;

        //Index starts at 1 to skip the header row
        for (var j = 1; j < sheetValues.length; j++) {
            var row = sheetValues[j];

            var someDate = new Date(row[0]).toDateString();
            if (isSameDateString(today, someDate)) {
                totalDonors++;
				/*
				The second argument, 10, is referring to the radix. Without specifying the radix,
				leading zeroes will break the summation and return NaN.
				This helps control for bad user inputs.
				*/
                var someDonation = row[4];
                totalDonations += parseInt(someDonation, 10);
            }
        }
        myHtmlBody += '<tr>';
        myHtmlBody += '<td>' + sheetName + '</td>';
        myHtmlBody += '<td>' + totalDonors + '</td>';
        myHtmlBody += '<td>' + totalDonations + '</td>';
        myHtmlBody += '</tr>';
    }

    myHtmlBody += '</table>';
    return myHtmlBody;
}

/***************************
*Function: onFormSubmitDebug
*Description: Sends an email containing form data. This is useful for debugging.
*@param {Event Object}: e
*@returns {Nothing}: N/A
***************************/
function onFormSubmitDebug(e) {
    var json = JSON.stringify(e, null, "\t");
    GmailApp.sendEmail('REDACTED@REDACTED.ORG', 'Debugging Info', '', {
        htmlBody: json
    })
}