Aggregate Report

ar.js

/**************
*Function: onFormSubmitDebug
*Description: Sends an email containing form data. This is useful for debugging.
*@param {Event Object}: e
*@returns: N/A
**************/
function onFormSubmitDebug(e) {

    var target_id = "REDACTED";
    var target_spreadsheet = SpreadsheetApp.openById(target_id);
    var target_sheet = target_spreadsheet.getSheetByName('Sheet1');
    var row = e;
    target_sheet.appendRow(row.values);

}

/**************
*Function: mailMonthlyReport
*Description: Mails out an Excel spreadsheet containing all Aggregate data. 
*@param: N/A
*@returns: N/A
**************/
function mailMonthlyReport() {

    var myHtmlBody = '<p style="font-size:30px; font-family: Brush Script MT; color: #D5AD6D;">';
    myHtmlBody += "Aloha,";
    myHtmlBody += '</p>';
    myHtmlBody += '<p>';
    myHtmlBody += "Attached to this email is an excel spreadsheet containing all recorded donations for last month.";
    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 last month.", "The XLSX file is attached", { htmlBody: myHtmlBody, attachments: [blob] });
    } catch (fail) {
        Logger.log(fail.toString());
    }
}

/**************
*Function: cleanUp
*Description: This algorithm continuously looks at the timestamp in the first column, second row of a spreadsheet.
              If the month (as a number) is smaller than the month of today's date, the row is deleted and the
              algorithm is rerun. If the timestamp's month is equivelant to today's month, the loop is broken out of.
*@param: N/A
*@returns: N/A
**************/
function cleanUp() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];

    while (true) {
        var row = sheet.getDataRange().getValues()[1];
        var someDate = new Date(row[0]).toISOString().slice(5, 7);
        var today = new Date().toISOString().slice(5, 7);

        if (parseInt(someDate, 10) < parseInt(today, 10)) {
            sheet.deleteRow(2);
        } else { break; }

    }
}


/**************
*Function: cleanUpRange
*Description: The more efficient version of cleanUp().
*@param: N/A
*@returns: N/A
**************/
function cleanUpRange() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var howMany = 1;
  
    while (true) {
        var row = sheet.getDataRange().getValues()[howMany];
        var someDate = new Date(row[0]).toISOString().slice(5, 7);
        var today = new Date().toISOString().slice(5, 7);

        if (parseInt(someDate, 10) < parseInt(today, 10)) {
          howMany++;
        } else { break; }

    }
  sheet.deleteRows(2, howMany-1);
}