Help in App Script (Convert to PDF and Email straightway)

Ask for Help
1

Hi Glider,

I’m using Glide to have data in a few sheets.
I create a template using google sheet itself convert to PDF and straight away email the user using this script.

var changedFlag = false;
var TEMPLATESHEET=‘Boom-Report’;

function emailSpreadsheetAsPDF() {
DocumentApp.getActiveDocument();
DriveApp.getFiles();

// This is the link to my spreadsheet with the Form responses and the Invoice Template sheets
// Add the link to your spreadsheet here
// or you can just replace the text in the link between “d/” and “/edit”
// In my case is the text: 17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI
const ss = SpreadsheetApp.openByUrl(“Service Apps - Google Sheets”);

// We are going to get the email address from the cell “B7” from the “Invoice” sheet
// Change the reference of the cell or the name of the sheet if it is different
const value = ss.getSheetByName(“Source Email-Boom”).getRange(“X3”).getValue();
const email = value.toString();

// Subject of the email message
const subject = ss.getSheetByName(“Source Email-Boom”).getRange(“B3”).getValue();

// Email Text. You can add HTML code here - see ctrlq.org/html-mail

const body = “Boom Lifts Inspection Report - Sent via Auto Generate PDI Report from Glideapps”;

// Again, the URL to your spreadsheet but now with “/export” at the end
// Change it to the link of your spreadsheet, but leave the “/export”
const url = ‘https://docs.google.com/spreadsheets/d/1NVJOdFLBAgNFqSHhnHJYybjUlSqhv4hKI_HXJyhJ88E/export?’;

const exportOptions =
‘exportFormat=pdf&format=pdf’ + // export as pdf
‘&size=A4’ + // paper size letter / You can use A4 or legal
‘&portrait=true’ + // orientation portal, use false for landscape
‘&fitw=true’ + // fit to page width false, to get the actual size
‘&sheetnames=false&printtitle=false’ + // hide optional headers and footers
‘&pagenumbers=false&gridlines=false’ + // hide page numbers and gridlines
‘&fzr=false’ + // do not repeat row headers (frozen rows) on each page
‘&gid=671631174’; // the sheet’s Id. Change it to your sheet ID.
// You can find the sheet ID in the link bar.
// Select the sheet that you want to print and check the link,
// the gid number of the sheet is on the end of your link.

var params = {method:“GET”,headers:{“authorization”:"Bearer "+ ScriptApp.getOAuthToken()}};

// Generate the PDF file
var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();

// Send the PDF file as an attachement
GmailApp.sendEmail(email, subject, body, {
htmlBody: body,
attachments: [{
fileName: ss.getSheetByName(“Source Email-Boom”).getRange(“B3”).getValue().toString() +“.pdf”,
content: response.getBytes(),
mimeType: “application/pdf”
}]
});

// Save the PDF to Drive. The name of the PDF is going to be the name of the Company (cell B5)
const nameFile = ss.getSheetByName(“Source Email-Boom”).getRange(“B3”).getValue().toString() +“.pdf”
DriveApp.createFile(response.setName(nameFile));
}

function onChange(e) {

var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var SheetName = ActiveSheet.getSheetName();
Logger.log (“ON0”);
Logger.log (“SHEET:” + SheetName);
if (SheetName===TEMPLATESHEET) {
Logger.log (“ON1”);
//the below will prevent onChange function to run multiple times
if(changedFlag == true) {
changedFlag = false;
return;
}
//only run functions if changedFlag is False
if(e.changeType === ‘EDIT’) {
Logger.log (“ON2”);
emailSpreadsheetAsPDF() ;
changedFlag = true;
}
}
}

My problem is, I need to be triggered when only on one sheet changes.
How can i do that?
Currently, they triggered when there’s a changes in the whole workbook.

1 Like
2

Use a wrapper function as your onChange() trigger.
That function should call e.source.getActiveSheet().getName(); to find out which sheet triggered the change.
If it’s the sheet you care about, run the rest of your script/s.
If it isn’t, do nothing.

Here is a simple example:

function on_sheet_change(event) {
  var sheetname = event.source.getActiveSheet().getName();
  var sheet = event.source.getActiveSheet();
  
  if (sheetname == 'moo') {
    do_something_with(sheet);
  }
}
1 Like
3

Thanks for your prompt reply Darren,

But should remove my current OnChange function?

4

Just edit it and point it at your wrapper function.

5

It is something like this ?

function onChange(e) {

var sheetname = event.source.getActiveSheet().getName();

var sheet = event.source.getActiveSheet();

Logger.log (“ON0”);

Logger.log (“SHEET:” + SheetName);

if (sheetname == ‘Boom-Report’) {

Logger.log ("ON1");

//the below will prevent onChange function to run multiple times

if(changedFlag == true) {

  changedFlag = false;

  return;

}

//only  run functions if changedFlag is False

if(e.changeType === 'EDIT') {

    Logger.log ("ON2");

     emailSpreadsheetAsPDF() ;

  changedFlag = true;

}

}

}

6

i would also check if active cell value is not empty to avoid triggering when deleting a row

7

Thanks Uzo for replying ,
But what do you mean?
I’m taking this script from google.
I understand how its work, but cant really know exactly what each of script means.

8

No, that won’t work.

  1. You defined the function using e, but then you try to reference event. So that will cause the function to immediately fail.
  2. Your use of changedFlag will cause emailSpreadsheetAsPDF() to be called once, and then it will NEVER be called again.

Try something like this:

function onChange(event) {
  var sheetname = event.source.getActiveSheet().getName();
  if (sheetname == 'Boom - Report') {
    if (event.changeType === 'EDIT') {
      emailSpreadsheetAsPDF();
    }
  }
}
1 Like
9

when deleting a row, in that sheet trigger on change will fire… so you can add to the script activeCell != “”
so it will ignore empty change

1 Like
10

Actually, you didn’t even define changeFlag, so your script will fail as soon as it hits the line that tries to check that.

1 Like
11

This is no need?

12

It depends how you plan to call the second function.
I generally use it, and then call the second function passing the sheet object.
This saves me having to do something like:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

in my called function.
If your called function already does that, then it isn’t necessary.

2 Likes
13

Hmmm okay. Not really clear. But i’ll try to put both. :slight_smile:

14

Hi again,

Regarding to the above script, do you know how to save in a folder in Gdrive.
Currently, it saved not in any folder. So , it get quite mess.

15

I have an example somewhere. I’ll dig it out and post later.

1 Like
16

You just need to identify the folder ID, then you can move the file to that folder.
I have code that does this, but I don’t think sharing it with you will help much, as it’s for very specific use cases. I don’t have any code that you can just copy/paste and use.

My advice is to read up on the required methods, gain an understanding of how they work, and write the code yourself.

2 Likes