Creating a doc from a sheet online

[JamesWB]

I seem to be hitting a barrier. I assume it's correct that Appsheet can't make a Google sheet generate a Google Doc from a template with completed values on command? This is presumably something that has to happen from a Google script inside the sheet?

I was thinking of alternatively using Excel as my base sheet and then generating a Word doc via VBA but of course can't do this from the Google cloud.

Would welcome any suggestions. My problem is that I need to launch the new Google Doc when a specific field in the Sheet is changed in value, but I can't see anything in the documentation for Triggers that allows this.

[WillowMobileSys]

It would help greatly if you could describe what you need to happen in a little more detail.

You can use Google Docs as templates to generate a final Google Doc or PDF.

The template is designed to take in row data from AppSheet and write it out in the specified formatted way.  You can mix row data with static text to generate the final document.

https://help.appsheet.com/en/articles/5280407-using-google-docs-templates

[JamesWB]

Thanks for your reply!

I want to do the following:

* App user selects an option saying "Publish Document".

* Sheet value changes from nothing to "Publish".

* When that change happens, a Google Doc should be generated with templated fields filled from several subsheets within the sheet.

I don't quite understand how to publish a Google Doc. I've seen the help page you directed me to. In there it says:

"Create a Google Docs or PDF template, as described in Creating a template."

But on the Creating a template page it just talks about emails and doesn't mention Google docs.

Similarly, on the Bots dialogues, the content types for attachments are HTML, CSV, etc - no mention of Docs.

The bit I don't get is how to tell the Bot to load a Google template, but also, in that template, how to load fields from different names subsheets within the sheet.

[JamesWB]

And also in the help files is this paragraph that seems to indicate you can't actually generate a Google Doc?

"AppSheet reads the Google Docs template in HTML format and replaces the template variables in the HTML template with the data values you specify. The resulting HTML document becomes the email body or attachment. For email attachments, AppSheet converts the HTML document to PDF format before adding it as an attachment."

[WillowMobileSys]

The excerpt was specifically for email attachments, but still, you may be right.  Looking at the task to create a file I am only seeing these options available shown in the image below.  I always use PDF's but it seems strange that the Google Doc template can't produce a Google Doc.

Does your output have to be a Google Doc?

[JamesWB]

Yes, PDF is useless to me, I need to create an editable Google Doc. I assume I have to script this from the sheet?

[pbalerio]

Hi James:

Have you tried Autocrat mail-merge add-on for google docs? It is free for a certain amount of documents. Really easy to use.

It creates a gdoc from a template using data in a worksheet. It adds a number of columns to the right of the sheet used for managing the content of documents created. These extra columns have to be not shown in your app.

You can set it to wait until a certain column [Publish docuent] has a value of "Publish" and the document can be sent by mail (I dont know whether it can be published at the web).

It does not use data from several sheets, just from one, but that can be overcomed within the app.

What I could not do is to dynamically add a table from a gsheet in the google doc without manually updating the doc.

[JamesWB]

Thanks, I'll give it a try - from a preliminary look, I'd assumed it could only send an email, but that would be something I guess. This Appsheet tool is very limited currently, it's pathetic that you can't create a Google Doc from it, given that this is a key part of the Google product range alongside Sheet.

[JamesWB]

Now I discover I can use Autocrat, but only by running another complexity, Importrange. Getting so complicated I'm starting to wonder about using something other than Appsheet!

[Glen]

I agree that Appsheet should be able to create the Google doc directly. Perhaps a simpler path to an editable google doc is create the file from Appsheet as .html and save it to Google drive. Once there you can right click to "Open with" and select Google docs to convert it to an editable Google doc, edit it and save it as a Google doc or many other file types. I have done this with simple templates, not sure if you will hit any issues with more complex files.

[JamesWB]

Thanks for the suggestion. Unfortunately I have a business need to not have any manual steps in the process, I want to fire up a Google Doc (populated from the Google sheet) from a template automatically when a "Print Now" button is pressed in the App. This doesn't appear possible when there is any complexity, eg, relational data - Autocrat can't handle it and any solution would require a lot of scripting in Google Sheets which I can't do. Oh and the final killer - Google Docs can't display conditional cell background colours on a scripted basis! Absolute rubbish from a business point of view. Appsheet is great but I've been forced to use Office 365 for my solution instead.

[scott_robinson]

I think you are mistaken with this

---

@JamesWB wrote:

Oh and the final killer - Google Docs can't display conditional cell background colours on a scripted basis!

---

There is definitely a function to change the cell colour of a table and I use this myself to 'light up' specific table entries in a document that has been generated from script.

for (var x = 0; x < colourholds.length; x++) {

    body.findText(colourholds[x]).getElement().getParent().getParent().asTableCell().setBackgroundColor('#ffff00');

  }

[JamesWB]

That's interesting Scott, thanks - where do you use that function exactly please? Is it in the Google Doc template?

[scott_robinson]

You don't have to write the script in the document template.  In fact the new Apps Script Tasks feature in Appsheet needs you to use a standalone script file.

[Glen]

A logical step; did you have a suggestion as to what option might automate this process?