Can I generate a document (invoice, presentation, etc)?

Contents

• When would I want to generate documents?
• How to generate a document in Tables
• 1. Setting up the tables
• 2. Setting up Apps Script function
• 3. Setting up the bot action
• Debugging & troubleshooting

When would I want to generate documents?

Sometimes you need to generate some document artifacts to share with other collaborators or customers, for example:

• When we need to review a submission in an intake form process, generate a review document that nicely formats all the submission information so we can comment and collaborate on it.
• When we process an order in our tracker, generate an invoice document that lists all the products ordered for a customer order, which we can print or save to PDF to share with the customer.
• When a customer in our tracker reaches the "Pitch" stage, generate a pitch presentation customized with slides that has information specific to the customer, which the account manager can use in the demo.

This types of use cases are great for any situation where you might want a formatted Google Doc, Sheet, or Slides to edit or share with others.

How to generate a document in Tables

We do not have a native bot action for this yet; however, you can do this using our Apps Script bot action with some of the below sample script code to have something working in minutes!

The key components to this solution will be:

• A templated Google Doc, Sheet, or Slides to copy and fill out with information.
• A table with the data we will use to fill into the generated document.
• An Apps Script script, which we will use to run the logic to generate the document.

Note: the document generated will belong to the person who sets up the Apps Script bot action in the table, but can use Apps Script to share the document with others.

1. Setting up the table

We recommend having a table with at least a few columns set up to facilitate generating and linking to the document:

1. We need to have columns with information we want to fill into the generated document, for example this could be some Text and Number-type columns for Order#, Description, Vendor, Quote.
2. Next, we need a way to indicate when to generate the document, perhaps using a Checkbox-type column named "Create Doc", or using a particular value in a Dropdown-type column named "Status".
3. Finally, we likely want to have one last column to store the link to the generated document, this can be a Text-type column called "Document Link".

Once you have your table setup with the right columns and data, we can go over to Apps Script to quickly put together the script to generate the document, and then finally call the script from our bot action.

2. Setting up the template document

Next, we need to set up the intended final presentation of the content in the document. We recommend creating a Google Doc or Google Slides to use to format and lay out the content in the desired manner.

1. Create a new Google Doc or Google Slides (or copy from an existing one).
2. Fill out and style the document to match the desired look and feel.
3. In place of where you want the content to be inserted from the table, put in some unique text to indicate "variables" that the script can easily find and replace with the column values. For example, you can use the column names with some special symbols to indicate where the value should go: {{Order#}} or %%Description%%. You can use whatever text you like, and the basic script samples below will find all instances of the text and replace it later.
   
   An example of the "Project proposal" Docs template before replacing with variables:
   
   
   Example of the same proposal template doc after replacing content with variables:
   
    

4. Once you're done setting up the document template, take note of the document ID, which can be found in the browser address url between the two slashes: docs.google.com/document/d/<documentId>/edit. We will use this later in our script / bot action.

Note: you can set up multiple types of templates to have different styles of documents for use for different situations.

3. Setting up Apps Script function

1. Open script.google.com to access the Apps Script project list.
2. Create a new Apps Script project and give it a recognizable project name (so you can find it when selecting the script for your bot action later). 
3. Ensure you have the Tables API service enabled.
4. Then copy and paste one of the following code snippets below into your script to use: 
   NOTE: column names are case-sensitive, we will get the tableId, rowId, docId from bot variables.

 

Generate a document, replacing simple text with values from a row

// You can run this test function to make sure the script works
// before hooking it up to the Tables bot action. Make sure to
// replace the doc/table/rowId with your own values!
function testGenerateDoc() {
  const docId = '1PVEww5urFXdi8KMwQ2has0CMwCNbTWqGn1Bxa64Fy3E';
  const tableId = 'blh8fbkF8yte_i2GT9zOW1';
  const rowId = '8RKcziclm9C3X_PUtky_KL';
  generateDocFromTemplate(docId, tableId, rowId);
}

function generateDocFromTemplate(docId, tableId, rowId, emails) { 
  console.log('params:', docId, tableId, rowId, emails);

  // Sanity check
  if (!(docId && tableId && rowId)) { 
    console.error('Missing or invalid doc ID, table ID, or row ID'); 
    return; 
  }

  // Get the table row data to generate the doc
  const rowName = 'tables/' + tableId + '/rows/' + rowId;
  const row = Area120Tables.Tables.Rows.get(rowName);

  // [OPTIONAL] Check the table row for an existing document link
  // This code is commented out for now, but you can uncomment it
  // to use it, if you need to ensure docs aren't generated twice
//   let docLink = row.values['Document link'];
//   if (docLink && docLink.length > 0) {
//     console.error('Doc link already exists, aborting');
//     return;
//   }

  // Get the template doc and make a copy of it
  const templateDoc = DriveApp.getFileById(docId);
  console.log('template name:', templateDoc.getName());
  const copyDoc = templateDoc.makeCopy();
  const copyId = copyDoc.getId();
  console.log('copy id:', copyId);
  const resultDoc = DocumentApp.openById(copyId);

  // [OPTIONAL] Share the new document with others to the
  // email addresses passed in via the "emails" parameter
  // Emails must be passed as a comma-separated list of
  // addresses (no spaces), which we will split into an array
  if (emails && emails.trim().length) {
    console.log('sharing doc with: ', emails);
    resultDoc.addEditors(emails.split(',').map(e => e.trim()));
    // Note: you can also replace .addEditors with 
    // .addViewers if you want view-only access
  }

  // Get column values to update doc with, and update the document name
  // Here you can customize what to include in the file name
  const data = row.values;
  const dateToday = new Date();  // Get today's date
  resultDoc.setName('Invoice for ' + data['Vendor'] + ' - ' + dateToday.toDateString());

  // Replace variables in doc using data from the row's columns
  // Need to match the variable with the data you want to replace with
  // If the data is empty or blank, can specify some error text
  const body = resultDoc.getBody();
  body.replaceText('{{Order#}}', 'Order #' + data['Order#'] || 'ERROR');  // Replace with Order#, or 'ERROR'
  body.replaceText('{{Invoice Date}}', dateToday.toDateString());         // Replace with a calculated value
  body.replaceText('{{Vendor}}', data['Vendor'] || 'ERROR');
  body.replaceText('{{Description}}', data['Description'] || '');         // If data is missing, leave blank
  // add more fields as you need them
  
  // Update the right column with the doc link
  row.values['Document Link'] = resultDoc.getUrl();
  Area120Tables.Tables.Rows.patch(row, rowName);
}

Generate a presentation, replacing text and images with values from a row

// You can run this test function to make sure the script works
// before hooking it up to the Tables bot action. Make sure to
// replace the doc/table/rowId with your own values!
function testGenerateSlides() {
  const slidesId = '19SDIqohRd9UttDb5zd6fK5K4beIruIZ6nxKCL1XtpsA';
  const tableId = 'blh8fbkF8yte_i2GT9zOW1';
  const rowId = '8RKcziclm9C3X_PUtky_KL';
  generateSlidesFromTemplate(slidesId, tableId, rowId);
}

function generateSlidesFromTemplate(slidesId, tableId, rowId, emails) { 
  console.log('params:', slidesId, tableId, rowId, emails);

  // Sanity check
  if (!(slidesId && tableId && rowId)) { 
    console.error('Missing or invalid doc ID, table ID, or row ID'); 
    return; 
  }

  // Get the table row data to generate the doc
  const rowName = 'tables/' + tableId + '/rows/' + rowId;
  const row = Area120Tables.Tables.Rows.get(rowName);

  // [OPTIONAL] Check the table row for an existing document link
  // This code is commented out for now, but you can uncomment it
  // to use it, if you need to ensure docs aren't generated twice
//   let docLink = row.values['Document link'];
//   if (docLink && docLink.length > 0) {
//     console.error('Doc link already exists, aborting');
//     return;
//   }

  // Get the template doc and make a copy of it
  const templateDoc = DriveApp.getFileById(slidesId);
  console.log('template name:', templateDoc.getName());
  const copyDoc = templateDoc.makeCopy();
  const copyId = copyDoc.getId();
  console.log('copy id:', copyId);
  const slides = SlidesApp.openById(copyId)

  // [OPTIONAL] Share the new document with others to the
  // email addresses passed in via the "emails" parameter
  // Emails must be passed as a comma-separated list of
  // addresses (no spaces), which we will split into an array
  if (emails && emails.trim().length) {
    console.log('sharing doc with: ', emails);
    slides.addEditors(emails.split(',').map(e => e.trim()));
    // Note: you can also replace .addEditors with 
    // .addViewers if you want view-only access
  }

  // Get column values to update doc with, and update the document name
  // Here you can customize what to include in the file name
  const data = row.values;
  const dateToday = new Date();  // Get today's date
  resultDoc.setName(slides.setName(data['Vendor'] + ' Pitch - ' + dateToday.toDateString()););

  // Replace variables in doc using data from the row's columns
  // Need to match the variable with the data you want to replace with
  // If the data is empty or blank, can specify some error text
  // Learn more about how to do this with Slides API:
  // https://developers.google.com/slides/how-tos/merge
  slides.replaceAllText('{{Vendor}}', data['Vendor'] || 'ERROR');       // Replace with Vendor, or 'ERROR'
  slides.replaceAllText('{{Invoice Date}}', dateToday.toDateString());  // Replace with a calculated value
  slides.replaceAllText('{{Quote}}', data['Quote'] || 'ERROR');
  slides.replaceAllText('{{Description}}', data['Description'] || '');  // If data is missing, leave blank
  // add more fields as you need them
  
  // For inserting images, we will replace any shapes in the slides
  // that have specific text with images specified by url. We need 
  // to use the Slides 'batchUpdate' API, and need to add the 
  // "Google Slides API" service (like we did for "Area120Tables").
  // We can use Tables "File attachments" columns, but we need to 
  // get the image URL (we use expiring urls for security reasons).
  function getRedirectUrl(url) {  // Helper function
    let response = UrlFetchApp.fetch(url, {'followRedirects': false, 'muteHttpExceptions': false});
    let redirect = response.getHeaders()['Location'];     // undefined if no redirect
    return redirect ? getTablesImageUrl(redirect) : url;  // check for nested redirect
    
  };
  let requests = [
    {
      replaceAllShapesWithImage: {
        imageUrl: getRedirectUrl(data['Logo'][0].url),  // 'url' of first attachment in 'Logo' column
        containsText: { text: '{{logo}}' }              // replace if shape has '{{logo}}' text in it
      }
    }, 
    { 
      replaceAllShapesWithImage: {
        imageUrl: 'https://storage.googleapis.com/gweb-uniblog-publish-prod/images/CT_BunnellHighSchool_1200px.max-1000x1000.jpg', // Public image urls
        containsText: { text: '{{headshot}}' }
      },
    } 
    // You can add multiple shapes to replace, please
    // note the curly brace structure, you must use
    // the right number of curly braces for each request
  ];
  console.log(Slides.Presentations.batchUpdate({requests}, copyId));
  
  // Update the right column with the doc link
  row.values['Document Link'] = slides.getUrl();
  Area120Tables.Tables.Rows.patch(row, rowName);
}

Generate an order invoice document with a table, pulling in data from multiple linked rows

<WIP, please come back later for this code sample>

Generate a project status update email draft, pulling in data from multiple linked rows

<WIP, please come back later for this code sample>

Generate a project review presentation, creating slides for each linked row

<WIP, please come back later for this code sample>

 

** IMPORTANT NOTE: before setting up the bot action, make sure to click the "Run" toolbar button to authorize the script for permissions you need. 

3. Setting up the bot action

Finally, in the table we set up earlier, add a Bot to use the "Execute Apps Script function" bot action, and select and authorize to use the new script we've just created, and make sure to add the parameters, in the right order, to pass along the right information to the function:

1. the Docs or Slides document ID
2. the table ID (use the variable [[table_id]])
3. the row ID (use the variable [[record_id]])
4. ... (whatever other parameters are needed)

For example, if the script function we are using is generateSlidesFromTemplate(), the function definition looks like:

  function generateSlidesFromTemplate(slidesId, tableId, rowId, emails)

so the parameters we define in the bot must match in order:

1. Slides document ID
2. table ID
3. row ID
4. email addresses of collaborators to share document with (can use Person-type column variable {{colName}} )

 

NOTE: the order of the function parameters you list in the bot matters, and needs to match up with the function definition in the script.

Debugging & troubleshooting

If your bot isn't working like you expect, here are some steps to determine what might be going wrong:

Check that the bot is enabled and set with the correct triggers:

• For time-based: if no bot conditions/filters are set, then the bot may trigger for every row.
• For column-changed: if the trigger column is deleted/changes type, you'll need to reconfigure the trigger.

If using an "email"-type bot action:

• Make sure recipients are set correctly, they need to be valid email addresses, or Google accounts (via a Person-type column).
• Make sure email subject and body are valid and don't have incorrect use of variables.
• You can test that emails are being sent correctly by manually entering your own email address first.

If using an "add/update row"-type bot action:

• Make sure any values set are correct for the destination column type.
• Try "clearing" the value and re-setting the values that are not carrying through or may have been using variables from columns that were deleted or had column type changed.

If using an "execution Apps Script" bot action: 

• Make sure the correct script is selected and the correct parameters are passed in the correct order.
• Make sure the bot execution history to see if there were any errors or failure messages:
  
  
   
• Make sure the Apps Script execution history for errors and debugging messages:
  
• Try other common troubleshooting steps for Apps Script, and reach out to the larger public Apps Script community, which is a great resource for quick questions and answers from other community experts.