Bot action: Execute Apps Script function

Contents

1. What can Apps Script do?
   • See detailed examples & walkthroughs
2. How to set up the Apps Script action
3. Security & debugging

What can Apps Script do?

The "Execute Apps Script function" bot action allows you to significantly expand the functionality of your Tables bots to run custom logic that you can define using Apps Script (instructions to enable Apps Script integration). This allows you to do things like:

• Every week, run a script to generate a Google Doc or Gmail draft that summarizes all the tasks done last week.
• Every week, go through all unassigned tasks and allocate them to balance workload for the team.
• When a checkbox is checked, generate an invoice for the customer with all the orders they made.
• When a lead is marked as "Prospect", generate a customized slide deck and intro email to pitch the product.

Note: Apps Script may be disabled for some organizations. Contact your admin for access issues.

Detailed examples & walkthroughs:

• Calculate values (formulas alternative), like adding dates or multiplying numbers
• How to batch create, update, and delete rows from a table
• Pull in data from other services (Ben Collins, Google Sheets Developer & Data Analytics Instructor) 
• Update a linked record in a linked table
• Generate a document (invoices or presentations) from a template

How to set up the Apps Script action

1. When you configure the bot to run an Apps Script function, you will need to select the desired Apps Script script, and specify the function you want to call.

 

 

2. If you are using special functions and APIs in your script, you will be asked to authorize the script for security reasons.

 

 

3. You can specify data to send to the script's function as parameters by clicking the "Add parameter" button. You can add multiple parameters, and each parameter will be sent to the function in the defined order, as text strings. You can insert data from your table and rows using column or metadata variables along with hardcoded text.

In the below example, if the function myFunction supported parameters myFunction(paramA, paramB), then paramA = {{Task}} and paramB = {{Due?}}.

 

 

4. In your Apps Script function, you will be able to retrieve these parameters by including them in the parameter definition for the function:

function myFunction(taskName, dueDate) {

  console.log(taskName);

  console.log(new Date(dueDate));

}

 

5. Finally, when saving the bot, the table will store which user configured the bot, so that the script can be executed consistently as the user, without having to re-authorize for every collaborator (similar to publishing Apps Script to "execute as me"). This allows you to ensure that the experience is consistent, especially when pulling data from other services or sending email communications (emails are sent from same address, or drive files have the same owner).

Security & debugging

For security and privacy reasons, there are some protections put in place:

• If the bot is edited, the user will have to authorize the script and when saved, the bot will execute as the new user. This is to prevent a malicious actor for switching out the script and accessing data they shouldn't.
• If the user who configured the bot no longer has access to the TABLE, then the bot will stop working and will not be able to continue executing as the previous user. Another user will need to re-configure the bot instead.
• If the user who configured the bot no longer has access to run the SCRIPT (following standard Apps Script access rules), then the bot will also stop working and will not be able to continue executing.

When an Apps Script bot action fails, an error will show in the bot history dialog, as well as in the script.google.com execution history.