Thanks Scott - I’ve been looking at Webmerge; it does the job I want it to do but it’s quite expensive considering the quantity of documents I’d need to process (around 500 per month). I need for every new record in a view to trigger the creation of a pdf document that would then be emailed automatically to a given recipient (also determined from data within the base). I’ve looked at google sheets and some add ons that can be added to that that would achieve the same output, but I’m concerned about google’s data protection policies. The data is sensitive personal data and I need it to be secure end-to-end, such that only the intended recipient can ever access it. Airtable data protection policies are much more robust in this regard I think.
I’ve done exactly this using Google Apps Script and G-Suite with full security measures for HIPPA.
The key is to start with a paid G-Suite account ($10/month) and use that as the basis for your web services development that taps into Airtable via its API.
Then ask Google to upgrade your G-Suite account to a security level that support HIPPA. They will do this on request and it’s no additional cost. It may also just be part of the G-Suite package now.
G-Suite also provides the automation framework for running serverless scripts and a fully-baked SDK for accessing documents that exist in Google Drive or accessing the data from Airtable and creating PDFs on the fly.
Lastly, the little cheap G-Suite account can email far more than 500 documents per day without cost and the domain those emails come from can be set up to be yours.
This approach is the only one that I’ve found reliable, secure, fully customized, and commercial grade (i.e., it looks and feels like your private solution).
Hi Bill. I think I have GSuite. What are the steps/clicks in airtable to get the page designer to convert to PDF and then email the PDF attachment? Hoping you can help. Thx.
There are no such steps to make that possible (that I’m aware of).
Unfortunately content in Page Designer stays in Page Designer unless you physically (and manually) use the features provided in the block to create a process with its content. There is no automation of printing, or queuing into another process for distribution. And there is no API into the content created by Page Designer.
Ergo, Page Designer is a nice friendly cul-de-sac.
The way I created an automated and highly visual process is to pull the data from Airtable (via the API) into the G-Suite environment using Google Apps Script. Then, using the native aspects of Google Docs and Slides I built various report templates with placeholders for the data including text narratives and charts.
Then I created a simple table (in Airtable) so users could administer automation properties such as how often a report was generated, who should receive it, and the template it should be based on.
Lastly, I built the scripting process to do it all without any manual process. This involves establishing repeatable processes that use Google’s integrated chron-job platform for running scripts at various intervals.
As you can see, this is not a product or a recipe, there are succinct steps and clicks anyone could follow. Rather, it is a solution crafted for a business that embraces automation process to make its workers productive and especially where data and visualizations are key to their communications success.
All I did was leverage the great elements of Airtable which the client loves, and make it possible to build and manage the reporting process far beyond anything we could ever expect Airtable to provide.
Here’s an example output. All of the data in these pages came from Airtable.
Awesome work around Bill - can you share how you did this?
It’s quite simple -
- Google Apps Script reads the Airtable data into an array structure (using the API).
- The array is transformed into various objects - titles, texts, charts.
- The objects are replaced into a Google Doc report template.
- The report template is exported into Drive as a PDF.
- The PDF is attached to a gmail message and dispatched to the designated recipients.
Thank you for writing about your workflow.
I’m no programmer but i would love to create a workflow like the one you describe here for ceating and sending invoices.
Could you explain me in a bit more detail how you achieved this?
With Google Apps Script you need only worry about using the vast features for document creation, and attaching documents to email messages that are sent automatically.
But that is a lot to choke down for a non-programmer. If you want to become proficient at this, I would start with this introduction by Ben Collins.
I would love to hand over the code for the project shown above, but it belongs to a company in the UK. It was originally developed in 2005 to use data in Google sheets and recently updated to pull its data from Airtable.
I understand. Thanks Bill. I’ll definitely try it out
Hey @Bill.French this is exactly what I need, I’ve been racking my brains trying to solve this for weeks. Do you do private work at all to set this up for me?
This thread is fantastic - thank you all! I have a slightly different use case, but wondering if you all have ideas for it. In my work I am regularly building presentations for multiple people to deliver. When we are building presentations, there are three elements that all need to be kept coordinated: the facilitator agenda, the presentation slides that correspond to the facilitator agenda, and the participant agenda. They all have some common elements like Section Names, times, links to resources, and there are some parts that only overlap between two of the three (ex: facilitator notes go on the facilitator agenda AND in the notes section of the slides). Revising the presentations and keeping all of these items coordinated is a major pain because of all the duplicate work that has to stay aligned.
Any thoughts about how I could build a “single source of presentation truth” in airtable that kicked out the different versions of that presentation?
This is no different than maintaining the latest version of a report document from a single source of data truth in Airtable. However, there are some nuances in the requirements – for example – are Google Presentations an adequate target for rendering these presentations? Please define “presentation”.
Unlike Microsoft Word (and PowerPoint), Google Apps Script offers a clean server-side interface/SDK for building, manipulating, and updating documents in real-time and from any data source including Airtable. Further, it provides a scripting model that supports templated documents across all Google Drive types (spreadsheet, document, presentation, and even charts). This makes it possible to FULLY automate every aspect of the document process from raw data (in Airtable) to finished native Google documents. The scripting model also supports transformations from Google’s native documents to PDF, Word, PowerPoint, etc.
And just when you thought there might be a show-stopper - Google Apps Script provides a number of SDKs for document delivery - through email, real-time messenging, shared Drive folders, and a few others.
The automation possibilities from Airtable data to the last mile of reporting consumption are pretty much endless. It’s just a simple matter of requirements and code.
This is fantastic - thanks Bill! Google presentation is actually the presentation tool we use due to the sharing/linking/updating features so that is perfect. I’m going to dig in on this - appreciate the heads-up on the scripting course site. Hope to report back with something good. I think this could be a really great tool for people - thanks!
Hello! I may have found a no code solution to this issue by utilizing Airtable > Zapier > Google Slides > Zapier > PDF > Zapier > Email/Upload.
Airtable trigger creates a Google Slides that is 8.5x11 size. Zapier Converts this to PDF. Zapier can then email this out or upload it back into airtable.
Zapier Google Slides Integration: https://zapier.com/blog/updates/840/google-slides-integrations
Slides to PDF: https://www.process.st/help/docs/create-a-pdf-with-zapier/
Hope this helps!
Yep - in today’s API economy there’s an abundance of glues and adhesives which make it effortless to pull together just about any process and data imaginable.
If you’re comfortable with 7+ hops to generate a single emailed report, this is one approach. But you must also accept the likelihood that with each additional dependency, your process inches a little higher up the brittleness ladder. Proceed with cautious optimism.
It may seem like I tend to discourage these glue-factory solutions, but I’m actually standing on the sidelines cheering with pom-poms for two reasons.
Tools like Zapier, Process Street, and Integromat stand at the headwaters of innovation. They make it possible for domain experts to make stuff work without coding expertise. New ideas and alchemies involving data, content, and process can be explored without significant cost. Mistakes have few consequences and lead to more learning and more innovation.
A significant and growing slice of my integration and data architecture consulting projects come from clients who have hit the limits of Goldbergian-sh*tshows and they’ve had it with reliability issues and the added cost associated with third-party solutions.
Gimmie a Z! Gimmie an A! Gimmie a P! …
API economy ? this link leads to
“API represents all segments of America’s oil and natural gas industry. Its more than 600 members produce, process, and distribute most of the nation’s energy. The industry supports 10.9 million U.S. jobs and is backed by a growing grassroots movement of millions of Americans. API was formed in 1919 as a standards-setting organization. In its first 100 years, API has developed more than 700 standards to enhance operational and environmental safety, efficiency and sustainability.”
I must’a been drunk when I copied that link. I probably meant this one.
In the course of October 2019, I wanted to practice by first discovering through a practical playground the basic possibilities of a workflow invoking Google App Script that is discussed in this Topic.
I experimented from these links 1 then 2 and I enjoyed starting with a simplistic but functional view of this type of approach before turning to a more systematic and complete learning recommended by Bill such as Ben Collins which is really an excellent reference!
Not a replacement for the page designer, but we’ve built an integration with Docupilot. It allows you to generate PDFs and upload them back in Airtable as attachments.