This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
Log in to ask a question
Log in to ask a question

Leverage AppSheet's New Apps Script Integration Capabilities and BigQuery Connector Enhancements

Posted on 04-20-2022 11:35 AM
Lauren_vdv
Community Manager
Community Manager
Reply posted on --/--/---- --:-- AM

This month, the Community came together for our April AppSheet Office Hours, featuring the new Apps Script integration capabilities and write functionality in the BigQuery data source.

Screen Shot 2022-04-20 at 12.16.43 PM.png

In this post, we synthesize the discussion, including how these feature updates help enhance app creation, including example use cases, supporting resources and app templates, plus answers to your frequently asked questions. Let’s dive in! 

Add custom integrations to your app using Apps Script

We get a lot of requests to have better and deeper Google integrations so that you can make better apps and more seamless experiences. Apps Script is Google’s low-code JavaScript platform that allows you to connect with and automate tasks across Google apps and services, including Google Workspace apps like Gmail, Calendar, Drive, and Chat, so you can create and automate more powerful workflows.

We’ve already seen some pretty creative AppSheet users come up with workarounds to hook into Apps Script today, usually by using our Webhook feature or Sheets triggers, but these approaches are usually insecure and clunky to use. Now, we have a native connector for AppSheet Automation to do this, and it opens up a world of possibilities for what you can do as a creator.

Here are just a few example use cases of how we’ve seen people create workflows using Apps Script to connect with the best of Google Workspace: 

  • Create events on a shared team calendar so a manager can see which shifts/appointment slots people are assigned and working on. What’s great about this is by adding these events directly to Google Calendar, you get a lot of extra things for free, like on-device push notifications, easy ability to see the events in context of the rest of your personal or work calendars, and ability to share the team calendar with other users for visibility.

  • Generate and populate a pitch deck in Slides or an invoice in Docs, and share the files with another Google user. If you have to do status updates or put together pitch decks, you can have an intake process via an AppSheet app, and then use Apps Script to generate slides and documents, fill it with images and text content from the record, and also adjust file permissions and even the file location in Drive.

  • Look up an employee’s manager and send a message to the manager for approval. Leverage the existing organizational structure from Google Workspace to do things like – look up an employee’s manager, or even go up the chain to find the first person with “Director” in their title, and send them an email to ask for approval for a request. 

  • Automatically add or remove a user from a Google Group to manage listservs. Another way to leverage Google Workspace’s existing organizational structure is to automatically add or remove someone from a Google Group so you can quickly and easily manage access and listservs.

See the live demonstration from @cschalk_ws, showing you how you can use the new Apps Script connector to build a travel request chatbot in AppSheet.

Supporting resources: 

Using data from BigQuery, now with write access

BigQuery is Google’s serverless, highly scalable, and cost-effective multicloud data warehouse, which is specifically useful for supporting analytical workloads.

Thanks to Community feedback and customer interest, we’ve added support for write functionality in the BigQuery data source. Previously, connected BigQuery sources were read-only, but as of today, you can perform full CRUD operations on any connected BigQuery data source.

This table breaks down the feature comparisons between BigQuery and Connected Sheets as AppSheet data sources:

Feature BigQuery Data Source Connected Sheets
Max number rows 100,000 rows plus security filters to select the most relevant rows first 10,000 rows
Subscription plan Enterprise-level AppSheet account, as BigQuery is considered an Advanced Connector (see details) Enterprise-level Google Workspace account (see details)
Initial setup Configure BigQuery in Google Cloud (more complex) Configure Connected Sheets feature in Sheets (simpler for non-technical users)
Sync time App is connected and synced in near real-time to the BigQuery dataset, providing access to the latest data Sheets sync on a schedule you configure - your app data may become stale between syncs

Here are a few examples of how you can use the BigQuery data source connector, now with write access: 

  • Update firmographic data in a table containing customer profiles

  • Submit weekly citywide COVID-19 hospitalization numbers via a tracking app to a nationwide dataset

  • Show machine-learning-recommended collateral from a BigQuery-powered sales enablement engine in an app at the point of sale – or automate sending it to a prospect in the field

  • Warehouse/store inventory counting and adjustments

See the live demonstration from @Rich_E, showing you how you can write data from AppSheet back to BigQuery.

This help article has been updated with full instructions for how to access and use this feature, including licensing requirements (Enterprise plans required), limitations, and best practices.

If you already have a writable BigQuery data source connected, you should be able to start writing data to it right away. Just be sure to mark the appropriate columns in the AppSheet Editor as “editable” so that they can write to your BigQuery table. 

Supporting resources: 

Questions and answers

For every AppSheet Office Hours, we make sure there’s time for you to ask questions and receive answers live. Below are written questions and answers to your most common questions so you can refer back to them at any time. There were also a few questions that we didn’t have time to get to on the call, but we did our best to add those to this post as well.

If you have any further questions or if you don’t see yours below, please add a comment to this post and someone from the AppSheet team or the Community will be happy to help!

1. What are the permissions needed to share and manage the script, such as in the Apps Script connector demo?

Typically, when you’re working with Apps Script and accessing different services (e.g. Gmail), you’ll get an authorization screen. So if you’re sending an email, for example, and hit save, you should see the typical Google Workspace authorization dialog that says, yes, this application will be executing as you and can send email on your behalf.

Additionally, if you’d like to share the script itself, it works just like a Google Drive file. So you can share it with others on your team with edit or view access. If for example, someone who is a bit more technical on your team can write the script, then they can share it with the rest of the team who may not be as technical and they can use it out of the box.

2. What coding language does Apps Script use?

JavaScript

3. I need an “import CSV” action from a parent table row to populate child table rows (child rows should have parent ID column automatically populated). Is this possible without Apps Script?

It depends on your particular setup, but generally speaking, you can use references between tables to create connections with disparate data. For example, you could connect the parent ID to the child rows by making it a reference.

In this episode of Building with AppSheet, @cschalk_ws shows you how to use multiple tables in AppSheet - allowing you to automatically recognize relations in data and apply references. Check it out to learn how you can use AppSheet to quickly identify relational data for your enterprise.

4. Can BigQuery connect to an existing AppSheet application?

Yes. If you have an existing application, you would follow the same steps as those if you’re creating a new application. More details on getting started are in the help documentation article, Using data from Google BigQuery with the BigQuery data source,  and in this Building with AppSheet episode: Connecting AppSheet to BigQuery (Note: since this video was created, the BigQuery data source now provides app creators with read-write access to BigQuery datasets, not just read access).

5. Has the 10,000 row limit increased?

AppSheet enforces the following limits for a BigQuery data source:

  • Maximum of 100,000 rows - BigQuery datasets exceeding the maximum number of rows will be truncated.

  • Partitioned tables are not supported - Any BigQuery tables that have partitioned columns will report an error during configuration in AppSheet. Use a BigQuery View that omits the partitioned column to work around this limitation.

  • Google Cloud access required - Service accounts are the only access method AppSheet supports. You will need to have access to create service accounts and keys in your Google Cloud project, or you will need to request assistance from an IT administrator with the appropriate level of access. The IT administrator can create the data source as a team data source and share it with you. 

This help article provides best practices for setting up your app to work within the product limits.

The 10,000 row limit only applies if you’re using data from Google BigQuery with Connected Sheets. 

6. How can I create a pdf that joins information from two tables?

You can use the Start expression in your report generation to essentially tell AppSheet to go and check another table and retrieve the data in that table. This is the recommended solution if you have a child table that has a REF type back to a PARENT table. AppSheet will generate this START expression in the template automatically. 

Another option is to create some virtual columns  in your primary report table. You can use functions like lookup() or select() or a Dereferences to populate data from another table in the primary table used for your report. This is usually the simpler option if you only have one or two columns to display in another table, whereas the Start expression is better suited for use cases with more columns where there is an established relationship between tables.

7. Does AppSheet handle the BigQuery structs and Array constructs? 

AppSheet will treat the arrays and structs as long text. As a result, you have limited ability to parse using only text operators such as split(), find(), etc.

8. Can NoSQL data be queried by BigQuery? Or is it only relational data? 

This is currently in Beta. You can query BigTable which is a noSQL database. 

9. If I try to connect AppSheet with Google Translate API, do I have to go through BigQuery? Or is there a way to connect directly? 

A relatively straightforward option is to use Google Translate in Sheets, which is demonstrated in the Office Hours replay here, and in this Tips & Tricks post here. 

You can also leverage APIs directly by sending webhooks to them and having something that comes back into the app. 

We recommend checking out this post from @Rich_E, which provides a multilingual starter app with support for 100+ languages! Plus, here’s an example app that uses this idea extensively. And finally, see this post on localization and how to change the language in your app. 

10. With write access to BigQuery, we’ll need some way to stage changes so they can be reviewed before they’re committed. Any suggestions?

To accomplish this, you would have some transactional table in the middle that would act as your workflow mediator - this could be a Sheet or some SQL database. Then, you’d use a combination of workflows to allow people to look at the BigQuery data, click an action that generates a form to that transactional table, edit, and then trigger a workflow, such as an approval routine or something of that nature. At the final stage of approval, once it’s been fully vetted, you can have an automation that then copies that data to BigQuery. A great way to help achieve this is with dynamic emails in Gmail. 

11. Is Apps Script integration only push, or can you also pull data?

Apps Script itself has full capabilities to push and pull data. When you set it up with the call script, you're sending the parameters and relying on whatever code you have on the Apps Script side to fulfill whatever you expect it to do. 

That can be to either call different services or push out a message to a chatbot, as shown in our live demo. So in that sense, yes, the integration is essentially calling, and you sending the data. 

But you could also get the effect of pulling data if you have code on the Apps Script side to push it back into an object that the AppSheet application is working with directly as well, such as updating a Sheet.

12. Can Apps Script return a JSON array and display it as a table in AppSheet?

There are a few ways you could accomplish this, depending on your use case and needs. In the current implementation, we do not support returning values directly from an Apps Script function, but here are a couple other approaches:

  • If you are using Sheets as your data source, you can use the Sheets API from Apps Script to have full control to take the JSON array and add or update rows in your table (and even format the Sheet or add new tables and columns, if you want).

  • If you just need to add or update rows in an existing table, you can use Apps Script’s URL Fetch Service to call the AppSheet API to pass the data back into the app.

13. How can you embed external website content within AppSheet apps?

As of today, there’s no way today to embed external websites into AppSheet apps. There’s nothing on the roadmap currently, but it’s good to hear this feedback.

It is possible however, to embed YouTube videos and other forms of iframed content into your app. See the following resources:

14. Can we have the ability to use a REST API directly from AppSheet?

You have two approaches you can take with this today:

  • AppSheet has the ability to call a webhook, essentially sending out an HTTP request. With this, the one thing you don’t necessarily have is the ability to process a response.

  • AppSheet can now also use Apps Script’s URL Fetch Service to call out to any third-party API. One of the benefits of Apps Script is that you can grab an OAuth token so you can do an authenticated OAuth request to a REST API and then process the results. You can take the result and push it back into something that the AppSheet frontend app is dealing with directly (directly into Sheets via the Sheets API, or back into the app using the AppSheet API). 

15. How can I generate reports and print them (or export to PDF) directly from AppSheet? 

If you’d like to create an attachment for an email, for example, there’s an option when you create an automation task to save a file to your Filestore. With that, you could pre-define the file name in the path to that. So it’s a combination of creating a task that can save that file to a specific location in your Filestore, and then have an action that is added to your views that says to download or open file. 

In general, with AppSheet Automation, you can generate events or tasks, including sending emails, push notifications, text messages, or generating documents. When you send an email or if you generate a document (e.g. PDF, HTML file, JSON file), you can use a Google Doc or Word template that has the variable fields of the data in your app.

We’ve also created this template app, which demonstrates how to download reports from the app directly. Check it out!

7 0 2,483
0 REPLIES 0