Combining Google Apps Script with Google AppSheet

Welcome !

In this blog I am going to show you how to combine Google AppScript with Google AppSheet to make automation even more powerful.

In one of my previous blogs, I showed you how to create an Inventory Management app using Google AppSheet. If you haven’t read it, check out the links below.

Create an Inventory Management App Using Google AppSheet Part — 1

Create an Inventory Management App Using Google AppSheet Part — 2

In the inventory Management App, I want to use Google Apps Script to send out emails to owner of the app as soon as a new product has been added to the inventory.

Sample Google Sheet

This is the Google Sheet that we will be using for this blog.

This sheet contains the following details:

1. Name of the Product
2. Category of the Product
3. Image of the Product
4. Initial stock of the Product
5. Restock Level of the Product

Step1: Google Apps Script Automation script

While you are in the Google Sheet, let’s open up the Script editor to write some Google Apps Script. To open up the script editor, follow these steps:

1. Click on extensions and open the Script Editor

2. This brings up the Script Editor.

We have reached the script editor, let’s code.

function main(Product,Category,InitialStock) {
    const sheet_ID = "1rLgHO-ECR8_mxJTnGjSM5hBOT1PgPY86EeHS6Ypb_Xs"
    const ss = SpreadsheetApp.openById(sheet_ID)
    const sheet = ss.getSheetByName("Inventory")

We start out by declaring a new function main() that accepts the following parameters:

1. Name of the Product
2. Category of the Product
3. Initial stock of the Product

const sheet_ID = "1rLgHO-ECR8_mxJTnGjSM5hBOT1PgPY86EeHS6Ypb_Xs"
const ss = SpreadsheetApp.openById(sheet_ID)
const sheet = ss.getSheetByName("Inventory")

Next we have declared the sheet ID that we want to access. After declaring the sheet ID, go ahead and open up the sheet using the SpreadsheetApp.openById() function by passing the sheet ID as a parameter. Next we are going to be getting the Inventory sheet by using the getSheetByName() function.

After getting the sheet, we are going to go ahead and create the body of the email.

const body = "Details of new product added" +  "<br><br>" +
"Product Name        : " + Product +  "<br><br>" +
"Category of Product : " + Category +  "<br><br>" +
"Initial Stock       : " + InitialStock +  "<br><br>";

Here we have created the email body containing all the details of the product.

const target_email = Session.getActiveUser().getEmail()

After declaring the body of the email, we move on to sending the email to the app owner. Instead of hardcoding the email address of the owner, we are going to be using the Session.getActiveUser() function and get the email address of the user using the getEmail() function.

const email_Subject = "New product added to Inventory";
MailApp.sendEmail(target_email,email_Subject,"",{htmlBody: body})
}

After getting the email address of the user, we move on to creating the email subject and sending the email to the app owner using the MailApp. For the email subject I have kept it very simple by writing, New product added to Inventory.

To send the emails, we are using the MailApp followed by using the sendEmail function inside which we are passing the following parameters:

1. Target Email
2. Subject of the Email
3. Body of the Email

Over script is ready and good to go.

Step2: Add AppScript inside AppSheet

Now that the script is ready and good to go, it’s time to add the script to the app in Google AppSheet.

To add the script to the app follow these steps:

1. Go to the Automation Tab in Google AppSheet

2. On entering the Automation tab, click on New Bot.

3. On clicking new bot, you will have to provide a name to the bot, followed by clicking on Create a custom bot.

4. On creating a custom bot, go ahead and click on Configure event.

5. On clicking configure event, you have to go ahead and give the event a name followed by specifying some conditions.

So in this case the event type is going to be Data Change and Adds only.I have done this because I only want the automation bot to run when a new product is added to the inventory.After specifying the conditions, I have specified the Inventory google sheet.

6. After specifying the event, go ahead and click on Add a step.

7. On creating a new task, we have to start out by giving the task a name. Next I have selected the Call a script feature.

Next we have specified the table name followed by specifying the Google AppScript project that we want to use.

8. After selected the Google Apps Script project, we have select the function that we want to run.

As we had talked before, I am going to be accepting parameters from the app that I have created in Google AppSheet. Inside the function parameters I am going to passing the the columns from the app.

Here you can see I have passed the columns from the app as parameters that the Google Apps Script code will accept.

Step3: Check the Output

We have everything setup and it’s time to check the output.

Here I have gone to the app and added a new product to my inventory. On clicking save, if I go back to my email I can see that the automation script has successfully worked and it should show you the following output.

Here you can see we have successfully used Google Apps Script inside Google AppSheet to send emails to the app owner as soon as a new product has been added to the inventory.

That’s all for this blog, I hope you have understood how to use Google Apps Script inside Google AppSheet. To know more about calling Google apps Script inside Google AppSheet check out the link below. I will be creating more content on using AppScript in AppSheet.

Call Apps Script from an automation

Feel free to reach out to me if you have any issues/feedback at aryanirani123@gmail.com