Appscript - Need help

Ask for Help
1

Hi everybody:)
I’m now need the appscript that help me to send email when page “Leave (Approved)” have changes.
So, i’m using this script but i’m not getting any email. Can you guys check whether my script is right ?

Summary 
var changedFlag = false;

var TEMPLATESHEET='Leave (Approved)';

function sendemailtoHR() {

  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("https://docs.google.com/spreadsheets/d/1QJfyJZt5_9s3Qh2xatz2OUPgDPtfiN-g-FeatEoGPFo/edit");

  // 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("Leave (Email)").getRange("P3").getValue();

  const email = value.toString();

  // Subject of the email message

  const subject = ss.getSheetByName("Leave (Email)").getRange("Q3").getValue();

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

  const body = ss.getSheetByName("Leave (Email)").getRange("R3").getValue();

  // 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/1QJfyJZt5_9s3Qh2xatz2OUPgDPtfiN-g-FeatEoGPFo/export?';

  

    // 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()}};

  
  

}

function on_sheet_change(event) {

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

  var sheet = event.source.getActiveSheet();

  

  if (sheetname == 'Leave (Approved)') {

    sendemailtoHR() ;

  } else return;

}

and i add the trigger:

image
image1366×768 149 KB

2

Note that when posting large chunks of code, you should enclose it in 4 backticks (````).
I fixed that for you.

3

I don’t see anything in your code that would actually cause an email to be sent.

4

Oh really ? haha . Actually i found the script on google since i know nothing about script.
The script that i found with Subject , Body and attachment.
But now, i only need the Subject and body. So, i delete a few things to have no attachment. Here is the original script.

Summary

var changedFlag = false;

var TEMPLATESHEET=‘M-Email Me’;

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(“https://docs.google.com/spreadsheets/d/1Zgs1jzjIeaBpd5Ms7emQgxhVJBMtlEOlDNDfxlhSRiY/edit”);

// 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(“M-Generate”).getRange(“F3”).getValue();

const email = value.toString();

// Subject of the email message

const subject = ss.getSheetByName(“M-Generate”).getRange(“B3”).getValue();

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

const body = “Automated Quotation - Sent via Auto Generate 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/1Zgs1jzjIeaBpd5Ms7emQgxhVJBMtlEOlDNDfxlhSRiY/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=true' + // do not repeat row headers (frozen rows) on each page

'&gid=101637384'; // 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("M-Generate").getRange("B3").getValue().toString() +".pdf",

        content: response.getBytes(),

        mimeType: "application/pdf"

    }]

});

// Save the PDF to Drive. (in the folder) The name of the PDF is going to be the name of the Company (cell B5)

const nameFile = ss.getSheetByName(“M-Generate”).getRange(“B3”).getValue().toString() +".pdf"

const folderID = “1SHKAXCyXmNMwv1QKPYW0QCwey-yzpJih”;

DriveApp.getFolderById(folderID).createFile(response).setName(nameFile);

}

function on_sheet_change(event) {

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

var sheet = event.source.getActiveSheet();

if (sheetname == ‘M-Email Me’) {

emailSpreadsheetAsPDF() ;

} else return;

}

5

yeah, that’s all a bit of a mess.
If you’re not experienced with Apps Script, then you’re probably going to have a tough time getting that working. What I would recommend is use something like Integromat for sending your emails. It’s way easier. I write a lot of Apps Script for my projects, but I almost never use Apps Script for sending emails.

6

i see…

7

Sending an email with Apps Script is actually fairly easy, all you need is something like this:

function send_mail_message(message, recipient, subject) {
  MailApp.sendEmail({
    to: recipient,
    replyTo: "me@example.com",
    subject: subject,
    body: message,
  });
}

The tricky part is processing your sheet and putting together the message (or body) part of the email.
Being able to help you with that would require an understanding of the structure of your sheets, plus a clear definition of the “rules” for what needs to be included. You haven’t shared any of that, so this is about as much help as I can give for now.

4 Likes
8

Darren,

What is param for?

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

9

That is constructing the headers for a HTTP request - a UrlFetchApp.fetch() call.

You can see that gets used in the second code sample that you posted, in the following line:

var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();

getBlob() means that it’s probably fetching a file - maybe a PDF file from GDrive? (it’s a bit difficult to tell)

10

How would one modify this script so that it emails a ‘range’ instead of the entire sheet?

@Uzo @Darren_Murphy sorry to bother. Any ideas?

i.e. (A1:P)

Summary

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

const ss = SpreadsheetApp.openByUrl(“https://docs.google.com/spreadsheets/d/ydr-mKa-gwiUIH-50x3L8RI_tPTjvvFGVs/edit”);

const value = ss.getSheetByName(“MSet”).getRange(“W2”).getValue();
const email = value.toString();
var start = format_date(ss.getRangeByName(‘FormatStartDate’).getValue());
var end = format_date(ss.getRangeByName(‘FormatEndDate’).getValue());

// Subject of the email message
const subject = ‘Full Breakdown’;

const body = “Full (” + start + " - " + end +")";

// 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/r-mKa-gwiUIH-50x3L8RI_tPTjvvFGVs/export?format=csv&gid=761753958’;

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

// Generate the CSV file
var response = UrlFetchApp.fetch(url,params).getBlob();
var start = format_date(ss.getRangeByName(‘FormatStartDate’).getValue());
var end = format_date(ss.getRangeByName(‘FormatEndDate’).getValue());

// Send the CSV file as an attachement
GmailApp.sendEmail(email, subject, body, {
htmlBody: body,
attachments: [{
fileName: “(” + start + " - " + end + “).csv”,
content: response.getBytes(),
mimeType: “application/csv”
}]
});
}

11

it is complicated… you have to create strings to convert to CSV, there are a few examples of that if you google it… but the easiest way is just to hide columns and rows that you don’t want to export :wink:

1 Like
12

Wow hiding columns I didn’t realize! I will give it try. Thank you very much.

13

I tried hiding the columns in GSheet using right click → hide columns but they still show up in Export/ CSV.

Am I missing something? Perhaps not possible this way when exporting a CSV?

14

It works on pdf, i did not try on csv… just open new sheet and copy ranges that you need…

1 Like
15

Have you considered the option of triggering the export from Glide via a webhook?
Or does it need to happen without user interaction?