Help - Apps Script

Hi,
I need help.
I’m using this script to convert the one page google sheet as attachment and email the user straightaway.

Now, how can I attach the one page as PDF AND add another attachment > the google sheet (one page only) itself so that the user can edit by themself.

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;

}

Fyi,

recipient will received 2 attachments > 1 pdf version , 1 excel version.

You may try Integromat or Zapier

Hmm for your information i make the apps script to trigger the actions which if user add form it will automatic email the user the PDF and now i need add attachment in excel format

Why send the sheet if the user could edit the sheet online or download it.
Hence, you probably could send a link to your sheet (or a cloned version) so that users could edit this sheet. This link could already be in the pdf attachment of your sheet.

Thank you for your response.

Like for example my sheet online cannot be access by the user because of a lot of data and the formula. I dont want them accidently delete or what.
So i want them to receive only one page.

Do you have any idea?

You could try sending two emails with one trigger. I have my trigger set to a Named Range ‘CSVTrigger’. Need to set column or increment that cell from glide.

function csv_sheet_change() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var trigger = ss.getRangeByName('CSVTrigger').getValue();
  if (trigger != '') {
    console.log("Sending CSV");
    emailSpreadsheetAsCSV();
    emailSpreadsheetAsPDF();
    ss.getRangeByName('CSVTrigger').clearContent();
  }
}

Thank you Eric !
Its works !
I make the user received only one sheet instead the whole worksheet.
Unfortunately, the formula is there.
Do you have the idea on how to download only the value & formatting before its email as attachment.

I have read this thread , but not really help for this current problem

@biha, maybe I don’t understand your use case, why do you want to send a google sheet to a user?
Could you explain the idea behind your application in more detail.

The original google sheet is only for admin because its have data of all users.
I dont want them to have access for online google sheet.
Once they add form in glide. Google sheet (apps script) will trigger and send email to the user.
So, they will received the page pdf and excel(only one page of all)

image

I still don’t understand your intention.
Do you want to send raw data from your google sheet, for what reason?
You could always clone the master table with SQL into another table showing only certain rows and use that table for your users.
But why not simply show the data in the app?
Would be helpful if you explain the whole story.

one page of my google sheet is template for quotation.
As picture below.
Before this, when user add form . Apps script will trigger and convert this template to PDF and user got copy.
But now user need a copy of only one page of google sheet .

Summary

Do you understand so far?

Ah…how it might work…
first duplicate that filled out quotation template (make a temporary clone), then create a new empty Google Sheet and copy the cloned table to the empty document, then share that Google Sheet with your customer, then delete your temporary clone. I think there is no need to attach a document to your email.

Have no script readily available, but I’m sure you’ll find some useful examples.

all process supposed to be automated.
How to make copy paste automated?