Generate Google Doc output

AppSheet Tips & Tricks
, ,
1

Several excellent posts on these forums around the subject of Google AppScript integration with appsheet. There are a variety of use cases for this. Here are some excellent examples and forum posts:

https://community.appsheet.com/t/solved-google-script-and-appsheet-integration/20270/8
https://community.appsheet.com/t/how-to-integrate-google-apps-script-trigger-with-an-appsheet-app/11805
https://community.appsheet.com/t/learning-resource-google-apps-script-and-cloud-technology/16388
https://community.appsheet.com/t/google-app-script-appsheet-api-write-data-to-spreadsheet-add-fields-name-to-row-1/28454
https://community.appsheet.com/t/executing-a-deeplink-action-from-an-email-silently/10992/2
https://community.appsheet.com/t/how-to-implement-functions-written-in-google-app-script-within-appsheer/24054/2

Yes, yes we all know it’s not very “no code” to add a google script to your appsheet solution no arguments there. Anyway, I’ve always been curious as to the inner workings of appscript. In my spare time recently, I figured out a way to create a Google Doc from appsheet data. Spoilers/Warnings:

  • this solution requires the AppSheet Rest API.
  • this is a code solution, it’s not for the faint of heart. You need some moderate understanding of app script.

This solution solves for a particular missing workflow output type of “Google Document”. I didn’t see anything on the forums on this, so hopefully folks find it useful. Perhaps at some point in the future AppSheet will natively be able to render a Google Doc (from a Google Doc template of course).

As a side effect, this solution also solves the fact that currently the AppSheet PDF output cannot include headers, footers or pagination. Something @Jonathon went to great lengths to solve in a different forum post.

Here’s the code and readme:

https://github.com/northwestcoder/googledoc_fromappscript_appsheet/tree/master/advanced

For you folks with massive talents on app script code greater than mine, let me know if you see anything that I could improve.

12 Likes
2

// these next few lines overcome a google drive limitation of always creating documents at the top level> // herein, we 1. create a doc, 2. copy it to a designated folder “outputfolderID” and 3. remove the original from the top level> // kind of janky

…still reading, but this gave me a chuckle. This is awesome though. Thanks for documenting all of this!

1 Like
3

Awesome! When I get a chance to get back to the AppSheet world I’ll definitely look into this.

I’m a huge fan of breaking the no-code boundary if the implementation seems reliable. Just adds more options.

3 Likes
4

I was planning to develop something similar but you beat me to it!! Thanks.

2 Likes
5

Hey Ty, You are really a smart man. I knew it when we met first time. A quick question. Is there a way to return the Created Google doc file ID to corresponding column so that we can access the URL inside of appsheet itself ? Thank you in advance.

6

We do this already in the example script:
https://github.com/northwestcoder/googledoc_fromappscript_appsheet/blob/master/advanced/main.gs

on line 42 we set the google ID:
activesheet.getRange(activeRow,6).setValue(docresponse[0]);

which was returned from the rest of the code sample, e.g. line line 172:
var results = [finalDoc.getId(), finalDoc.getName()];

4 Likes
7

Thanks a lot Ty. I will sure get back if i have any doubt.

8

Hey Ty,

Thanks for building this out. Is it a problem if my source tables all came as their own sheet? Is there a need to combine them into spreadsheet?

Also, it looks like the sample google docs template is restricted. I have requested access if you don’t mind.

Thanks! David