Skip to main content

Effective Use of Column Headers and Data Rows in Google Sheets

The first row of your data sheet must always contain the column titles, and each column in the header row will correspond to the variable field in your email or document template.

Here are some essential points to know while preparing your sheet columns for merge with Document Studio.

The Column Headers (Table Schema)​

  1. The column titles should always be in the first row of the sheet.
  2. No two column titles should contain the exact text ("First Name" and "first name" are considered duplicates, the same is for "email address" and "e-mail address")
  3. It is a good practice to move any columns without a name to the end or remove them.
  4. Avoid using any special characters in column titles like emojis.
  5. Please ensure that the cell range in the first row of the Google Sheet is unprotected.
  6. Provide meaningful and easy-to-recall names for your column headers. "Student's Name" and "Parent's Name" are more helpful than "Name 1" and "Name 2" headers.
  7. Avoid using dynamic column names whole value change over time (for instance, formulas that use Date functions)
  8. Use ARRAYFORMULAS to copy-down values as these formulas are always evaluated prior to running merge.

The Data Rows (Records)​

  1. The text in emails and documents will show up exactly as shown in the cells of your sheet (sans the formatting).
  2. If there are any formulas in your Google Sheet, they'll be evaluated before running the workflows.
  3. Document Studio treats each row in the spreadsheet separately and processes the entire row in one go.
  4. You can add any number of data rows to a Google Sheet and they will be processed in sequence starting from the second row (the first row is reserved for title names).
  5. Each cell in the spreadsheet can have only one value. If you enter multiple values in a single cell of a row, they'll be treated as part of the same row and not separate records.
  6. If a cell values contains a link to some file in Google Drive, the link should be of the form https://drive.google.com/open?id=1234 (multiple file URLs in the same cell can be separated with commas)
  7. For cell values containing checkboxes, they'll be evaluated to TRUE or FALSE depending on the state of the checkbox.
  8. Cell values containing numbers, phone numbers, dates, currencies, percentages and postal codes should be correctly formatted in the source spreadsheet itself.

The Sheet (Container)​

  1. Document Studio will add a new sheet titled Document Studio Logs to store any errors and debugging logs. Please do not edit this sheet.
  2. You many use any sheet (workbook) in the Google Spreadsheet for merge but the worksheet names should not have any special characters like emojis, quotation marks or slashes.
  3. If your Google Sheet has multiple workbooks, you can safely reorder them or hide the ones that are not required for merge.

Format Dates in Columns​

The add-on tries to determine the data type of a column based on the values returned by the spreadsheet. If a column contains dates or data time (like the timestamp when a Google Form is submitted), please ensure that the column is formatted in date format.

To change the column format, select the entire column, go to the Format menu, choose Number and then choose Date time. Once the date time format has been applied to the column, you can enter dates in short format - like Aug 2 3:30 pm and they'll be converted into the correct date+time format based on your Spreadsheet locale.

Date Format in Google Sheets