Document Merge using Google Apps Script, Webhook Action & Google Docs

[Purvang_Joshi]

Hello, Gliders,
Many of you have use-cases like,
→ Sending certificates to your students
→ Sending Auto-Generated Invoices, Quotes & Many more
→ creating ID cards for the events
→ Mail merge custom PDFs

Here I’ll show you how to implement it.

Demo: https://milky-produce-8069.glideapp.io/ [It’s in Personal Plan, Only 80 Webhook Actions Left]

Whenever the user fills and submits a button, it’ll trigger the script and send a pdf attached mail to the receiver.

The flow of the System

Glide App → Webhook Action → Google Apps Script → Google Docs → PDF Mail

Implementation Steps

Step: 1
Create a Google Doc Template (Source File) Make Copy from here for demo purpose Make a Copy of Google Doc

Now, open copied document, copy the document id from the URL. (Will be used later) (Don’t include /d/)

Step: 2
Make a Google Drive Folder (It’ll be used as temporary to store & delete files)

Copy Drive Folder ID (Will be used later)

Step: 3
Copy following code to Google Apps Script
Click here to make new script project script.new

function doPost(e) {


// Calling Parameters from GlideApp
const body = JSON.parse(e.postData.contents);
const ProjectName = body["params"]["ProjectName"]["value"];
const CompanyName = body["params"]["CompanyName"]["value"];
const YourName = body["params"]["YourName"]["value"];
const YourEmail = body["params"]["YourEmail"]["value"];
const Date = body["params"]["Date"]["value"];

// Copy IDs
const doc_id= "ENTER DOCUEMENT ID HERE";
const tempFolder_id = "ENTER DRIVE FOLDER ID HERE";

const template = DriveApp.getFileById(doc_id);

const tempFolder = DriveApp.getFolderById(tempFolder_id);
const doc_new = template.makeCopy(tempFolder).setName(ProjectName);
const doc_new_id = doc_new.getId();
const open_doc = DocumentApp.openById(doc_new_id);
const body_doc = open_doc.getBody();

// Replaces all values to new ones.

body_doc.replaceText("Project name", ProjectName);
body_doc.replaceText("Date", Date);
body_doc.replaceText("YOUR COMPANY NAME", CompanyName);
body_doc.replaceText("YOUR NAME", YourName);
body_doc.replaceText("YOUR EMAIL", YourEmail);

open_doc.saveAndClose();


const BLOBPDF = doc_new.getAs(MimeType.PDF);
//"Remove this to store pdf to temporary folder " tempFolder.createFile(BLOBPDF).setName(ProjectName);
tempFolder.removeFile(doc_new);

const senderName = "Project Proposal";
const subject = YourName + ", you're awesome!";
const body_mail = "Please find your proposal for "+ ProjectName + "attached";
GmailApp.sendEmail(YourEmail, subject, body_mail, {
  attachments: [BLOBPDF],
  name: senderName
});
SpreadsheetApp.flush();

}

Step: 3
Assign an Existing GCP Project to the settings of Apps Script or create a new GCP Project, Let me know if you're facing any difficulties.

Step: 4
Deploy GAS Project as a web app.
Copy Web App URL after deploying.
Important: Whenever you make changes to your code, you’ve to redeploy that code and change the URL.

Step: 5
Setup GlideApp Webhook Action to a button
Important: Check Spell twice before running the script.

Done!

You can also send pdfs via google slides. Let me know if anyone wants a tutorial on it.

[Robert_Petitto]

Very cool! For those that aren’t savvy with Google Scripts, Google sheets has a free add on called autocrat that will let you send mail merge Google docs just like this. It’s a bit buggy like anything else, but it’s a nice solution.

https://workspace.google.com/marketplace/app/autocrat/539341275670

[Purvang_Joshi]

Yes. Indeed. Autocrat is a great solution for very long.
This script will benefit to share of instant mail. It took 8 Seconds to receive a merged PDF as of now.

Also, Autocrat hasn’t instant trigger solution. This saves a lot of time for me.

[gvalero]

Hola @Purvang_Joshi

I wonder if your script can open the same doc to update it and later to be shown or sent instead of creating a new one?

I made some similar but using Integromat and Google Slices but Integromat always creates a new version of my template and I don’t want it.
I want to have an only one file to this process. Could you test it for me please?

Thanks for this good tip.

Feliz día

[Purvang_Joshi]

You can delete new generated file anyways. I’ve already added.

tempFolder.removeFile(doc_new);

We shouldn’t do with single slide because this script uses replace text method. The new generated will replace template fileds. So, it’ll create an error to next operation. Template will be overwrites, thats why copying a new doc and deleting itself should ne a good choice.

[gvalero]

Yes, I know I can delete older files but in my case, I need to use the same file (Slide) due to a Display/TV will show the newest info from my APP (Order #, Customer Name, Total $) at a restaurant.

That data is sent by my APP dynamically to my Slide and works perfect but I am looking for a way to get it keeping the same Slice on Display without refreshing or opening a new file manually.

Thanks again!

Saludos

[Purvang_Joshi]

Got it.
I’ll try and let you know. Nice use case.

[gvalero]

Gracias!

You can see what I try to do watching my post

A real notification using Glide and IoT

Hi people! Here I bring another tool to make Glide APPs more useful and smarter. Many of us have asked and looked for a solution to have a notification when a form, order or event is sent/received from our APP and some workarounds can be used depending on use case. In my case, I wanted to have a real notification beyond a phone or PC and turn on/off a display, monitor, alarm siren, etc when an event on my APP is fired and I think I have the solution using IoT technology and Webhooks. Here is …

But now, I try to improve the message sent by my APP.

Saludos

[Robert_Petitto]

Exactly. Autocrat is nice if you don’t mind the wait …

[Pratik_Shah]

That’s great @Purvang_Joshi Thank you so much for this.

In addition to PDF, can we a share Google Doc on email? I want to allow my user to edit the merged doc.

[Wiz.Wazeer]

Great

[Purvang_Joshi]

Hey, You cannot send the doc as an attachment.

Alternatively, you can add access to the newly created google doc.
doc_new_id.addEditor(“Mail ID”);

And don’t forget to remove this line “tempFolder.removeFile(doc_new);”

[Pratik_Shah]

Thank you

[Maryth_R]

Hi! Great work! Thanks for sharing! I am curious about sending pdfs via google slides too.

[Purvang_Joshi]

Hey @gvalero,
I’ve used a simple method to override the existing component from the last value.

Here I’ll be showing you the script for one component.

Open any slide and create a textbox and add value Name.
And in google sheet, set the cell header name the same as the textbox value name.

function TestFunction() {

var slideId = “SLIDE ID”;
var sheetId = “SHEET ID”;

var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(“Sheet1”);
var values = sheet.getDataRange().getValues();
const length = values.length;

for (let i=0; i<length; i++) {
if(sheet.getRange(i+2, 1).getValue() != ‘’){
if(sheet.getRange(i+2, 7).getValue() == ‘’){

var name = sheet.getRange(i+2,1).getDisplayValue();
var f_name = sheet.getRange(i+1,1).getDisplayValue();

var empSlide = SlidesApp.openById(SlideId).getSlides()[0];

empSlide.replaceAllText(f_name, name);
sheet.getRange(i+2,7).setValue(“Sent”);
}
}
}
}


[Purvang_Joshi]

This is the code for sending PDFs from Google Slides.

function doPost(e) {

// Calling Parameters from GlideApp
const body = JSON.parse(e.postData.contents);
const ProjectName = body["params"]["ProjectName"]["value"];
const CompanyName = body["params"]["CompanyName"]["value"];
const YourName = body["params"]["YourName"]["value"];
const YourEmail = body["params"]["YourEmail"]["value"];
const Date = body["params"]["Date"]["value"];

// Copy IDs
const tmp_slide_id = "SLIDE ID"; //Document ID
const tempFolder_id = "TempFolderID"; // Drive Folder ID

const template = DriveApp.getFileById(tmp_slide_id);

const tempFolder = DriveApp.getFolderById(tempFolder_id);
const new_slide = template.makeCopy(tempFolder).setName(ProjectName);
const new_slide_id = new_slide.getId();       
const OpenSlide = SlidesApp.openById(new_slide_id).getSlides()[0];

// Replaces all values to new ones.

OpenSlide.replaceAllText("Project name", ProjectName);
OpenSlide.replaceAllText("Date", Date);
OpenSlide.replaceAllText("YOUR COMPANY NAME", CompanyName);
OpenSlide.replaceAllText("YOUR NAME", YourName);
OpenSlide.replaceAllText("YOUR EMAIL", YourEmail);


const BLOBPDF = new_slide.getAs(MimeType.PDF);
//Remove this to store pdf to temporary folder// tempFolder.createFile(BLOBPDF).setName(ProjectName);
tempFolder.removeFile(new_slide);

const senderName = "Project Proposal";
const subject = YourName + ", you're awesome!";
const body_mail = "Please find your proposal for "+ ProjectName + "attached";
GmailApp.sendEmail(YourEmail, subject, body_mail, {
  attachments: [BLOBPDF],
  name: senderName
});
SpreadsheetApp.flush();

}


[gvalero]

Fine, let me test and I will let you know.

Gracias Joshi

[gvalero]

@Purvang_Joshi the solution is working!!

I modified your original script, added some tricks along with a Chrome’s Add-On and now it’s working like I wished.

Tomorrow I will try to show a video to celebrate!

Thanks for your idea.

Feliz día!

[gvalero]

Hi @Purvang_Joshi @SantiagoPerez

Here is my newest video using the Joshi’s idea with Google Slides

Here you can see:

1. Every new order sent from my APP is shown on TV as a tool to help any personnel to receive and accept incoming orders (at a restaurant, call center, etc). On TV, I show some info about order but I might put more data and other things (orders in queue, stats) if the customer needs it.
2. I’m using Google Slides to show the Order info on TV and a script to send data from my GS to Slides as soon as a new order is registered.
3. When all received orders are accepted by administrator and there are no pending, the APP sends a command to my IoT device to turn TV off. Then, if a new order is received again, the APP will send a new command to turn TV ON as a visual notification to personnel (I could use a bell/siren but I liked the TV idea ).

I hope it can help anyone in future and demonstrate that you can use Glide for this kind of things…

Saludos!

:

[Gregory]

Thank you for this tutorial @Purvang_Joshi !

For complex automations I can recommend zenphi.

best
Gregor