Emailing data from a spreadsheet at a specific time of day

Question

I am a complete novice with Google Scripts, and I need to find a way to have data collected in Google spreadsheet emailed to a Gmail account.

I have a Google Form set up that records customer service statistics throughout the day. The responses are collected into the spreadsheet. The spreadsheet is cleared everyday to prepare it for the next day's responses. What I need to find is a way to have all of the collected responses emailed in a report at the end of each day.

From the searches I have done so far, I believe that getRange and getValues are needed, but I don't know how to craft the code to retrieve all of the data on the sheet. The spreadsheet always has 4 set columns, but the number of rows generated depends on the number of statistics collected each day. I figured out that getLastRow can address this issue, but how do I use it with getRange and getValues?

I tried writing a very basic script to make this work, but all it does is email the value in the A1 cell. I need all of the data. I used a time trigger to control when it runs. what am I doing wrong?

function emailStats() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dataRange = sheet.getActiveRange().getValues();
  var subject = "Daily Report of Customer Service Statistics";
  MailApp.sendEmail("[email protected]", subject, dataRange);

}

Mogsdad Mogsdad · Accepted Answer · 2013-08-27T23:22:10

Changes:

getDataRange()
Generate an HTML table from the array retrieved by .getValues(). See Generate HTML table from 2D JavaScript array.

Code:

function emailStats() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var subject = "Daily Report of Customer Service Statistics";
  MailApp.sendEmail("[email protected]", subject, createTable(data));
}

Emailing data from a spreadsheet at a specific time of day

1 Answers