# Appscript - Need help
**URL:** https://community.glideapps.com/t/appscript-need-help/27139
**Category:** Ask for Help
**Created:** [May 24, 2021, 3:01am UTC](https://community.glideapps.com/t/appscript-need-help/27139 "2021-05-24T03:01:07Z")
**Posts on this page:** 15
**Page:** 1
### Author:  [@biha](https://community.glideapps.com/u/biha)
#### Post date: [May 24, 2021, 3:01am UTC](https://community.glideapps.com/t/appscript-need-help/27139/1 "2021-05-24T03:01:07Z")
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**
>
> ```auto
> 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:
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [May 24, 2021, 3:04am UTC](https://community.glideapps.com/t/appscript-need-help/27139/2 "2021-05-24T03:04:48Z")
Note that when posting large chunks of code, you should enclose it in 4 backticks (````).
I fixed that for you.
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [May 24, 2021, 3:07am UTC](https://community.glideapps.com/t/appscript-need-help/27139/3 "2021-05-24T03:07:16Z")
I don’t see anything in your code that would actually cause an email to be sent.
---
### Author:  [@biha](https://community.glideapps.com/u/biha)
#### Post date: [May 24, 2021, 3:09am UTC](https://community.glideapps.com/t/appscript-need-help/27139/4 "2021-05-24T03:09:14Z")
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](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?’;](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;
>
> }
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [May 24, 2021, 3:15am UTC](https://community.glideapps.com/t/appscript-need-help/27139/5 "2021-05-24T03:15:55Z")
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.
---
### Author:  [@biha](https://community.glideapps.com/u/biha)
#### Post date: [May 24, 2021, 3:18am UTC](https://community.glideapps.com/t/appscript-need-help/27139/6 "2021-05-24T03:18:15Z")
i see…
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [May 24, 2021, 3:30am UTC](https://community.glideapps.com/t/appscript-need-help/27139/7 "2021-05-24T03:30:26Z")
Sending an email with Apps Script is actually fairly easy, all you need is something like this:
```auto
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.
---
### Author:  [@biha](https://community.glideapps.com/u/biha)
#### Post date: [May 24, 2021, 3:32am UTC](https://community.glideapps.com/t/appscript-need-help/27139/8 "2021-05-24T03:32:24Z")
Darren,
What is param for?
> var params = {method:“GET”,headers:{“authorization”:"Bearer "+ ScriptApp.getOAuthToken()}}
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [May 24, 2021, 3:37am UTC](https://community.glideapps.com/t/appscript-need-help/27139/9 "2021-05-24T03:37:16Z")
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:
```auto
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)
---
### Author:  [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)
#### Post date: [December 23, 2021, 10:33pm UTC](https://community.glideapps.com/t/appscript-need-help/27139/10 "2021-12-23T22:33:09Z")
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](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](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”
> }]
> });
> }
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [December 24, 2021, 6:31pm UTC](https://community.glideapps.com/t/appscript-need-help/27139/11 "2021-12-24T18:31:30Z")
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 😉
---
### Author:  [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)
#### Post date: [December 24, 2021, 8:45pm UTC](https://community.glideapps.com/t/appscript-need-help/27139/12 "2021-12-24T20:45:58Z")
Wow hiding columns I didn’t realize! I will give it try. Thank you very much.
---
### Author:  [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)
#### Post date: [December 25, 2021, 5:55pm UTC](https://community.glideapps.com/t/appscript-need-help/27139/13 "2021-12-25T17:55:45Z")
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?
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [December 25, 2021, 6:24pm UTC](https://community.glideapps.com/t/appscript-need-help/27139/14 "2021-12-25T18:24:45Z")
It works on pdf, i did not try on csv… just open new sheet and copy ranges that you need…
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [December 26, 2021, 2:00am UTC](https://community.glideapps.com/t/appscript-need-help/27139/15 "2021-12-26T02:00:03Z")
Have you considered the option of triggering the export from Glide via a webhook?
Or does it need to happen without user interaction?