Efficient Way of sending Spreadsheet over email using GAS function?

user_11700769 0
Viewed 176

I am creating an addon for Google Sheets that my local High School's volunteer clubs can use to keep track of their member's volunteer hours. Most of the code is done and works very nicely, and I am currently working on a system that will send a member a spreadsheet listing all of the volunteer events that they have logged. I have GAS create a separate spreadsheet, and then send an email with that separate spreadsheet attached in PDF. When the email is received, the PDF is empty except for a singular empty cell at the top left of the page.

I am pretty new to GAS but have been able to grasp the content pretty easily. I have only tried one method of sending the Spreadsheet and that is by using the .getAs(MimeType.PDF). When I changed the "PDF" to "GOOGLE_SHEETS," GAS returned the error: "Blob object must have non-null data for this operation." I am not entirely sure what a Blob object is, and have not found any website or video that has fully explained it, so I am not sure how to go about troubleshooting that error.

I think I'm having a problem grabbing the file because it either sends an empty PDF or it returns an error claiming it needs "non-null data." 

function TigerMail()
{
  var Drive = DriveApp;
  var app = SpreadsheetApp;
  var LOOKUP = app.getActiveSpreadsheet().getSheetByName("Student 
  Lookup");
  var Name = LOOKUP.getRange("E1").getValue();
  Name = Name + "'s Hours";
  //app.openById(Name+"'s Hours");
  var HOURS = app.create(Name);
  var ESheet = HOURS.getSheets()[0];
  var ROW = LOOKUP.getLastRow();
  var arr = LOOKUP.getRange("D1:J"+ROW).getValues();
  var cell = ESheet.getRange("A1:G"+ROW);
  cell.setValues(arr);

////////////////////////////////////////////////////

  var LOOKUP = app.getActiveSpreadsheet().getSheetByName("Student 
  Lookup");
  var cell = LOOKUP.getRange("D1");  
  var Addr = cell.getValue();
  var ROW = LOOKUP.getLastRow();
  var file = Drive.getFilesByName(Name);
  var file = file.next();
  var FORMAT = file.getAs(MimeType.GOOGLE_SHEETS);

  TigerMail.sendEmail(Addr, "Hours", "Attached is a list of all of the 
  events you have volunteered at:", {attachments: [FORMAT]} );
}

the final four lines are where the errors are occurring at. I believe I am misunderstanding how the .next() and .getFilesByName() work.

(above the comment line: creating a spreadsheet of hours)

(below the comment line: grabbing the spreadsheet and attaching it to an email)

Here is the link to the Google Sheet: https://docs.google.com/spreadsheets/d/1qlUfTWaj-VyBD2M45F63BtHaqF0UOVkwi04XwZFJ4vg/edit?usp=sharing

google-apps-script google-sheets
1 Answers
  • In your script, new Spreadsheet is created and put values.
  • You want to sent an email by attaching the file which was converted from the created Spreadsheet to PDF format.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

Modification points:

  • About Drive.getFilesByName(Name), unfortunately, there is no method of getFilesByName() in Drive.
    • I think that when you want to use the created Spreadsheet, HOURS of var HOURS = app.create(Name) can be used.
  • About var FORMAT = file.getAs(MimeType.GOOGLE_SHEETS), in the case of Google Docs, when the blob is retrieved, the blob is automatically converted to PDF format. This can be also used for your situation.
  • In order to save the values put to the created Spreadsheet, it uses SpreadsheetApp.flush().

When above points are reflected to your script, it becomes as follows.

Modified script:

Please modify as follows.

From: 
var file = Drive.getFilesByName(Name);
var file = file.next();
var FORMAT = file.getAs(MimeType.GOOGLE_SHEETS);
To: 
SpreadsheetApp.flush();
var FORMAT = HOURS.getBlob();

Note:

  • In your script, it seems that var ROW = LOOKUP.getLastRow() is not used.

References:

If I misunderstood your question and this was not the result you want, I apologize.

user_71086530
Related