Google Sheets (Inbound)

Overview

This feature allows institutions to connect their Mainstay data to a third-party platform, in this case, Google Sheets. This enables an inbound contact record data sync, including default fields (name, phone, email, etc.) and custom fields created by your institution.

Setup

1. Navigate to the Settings > Google Sheets (Inbound) page. (Note: this requires admin permissions.)
2. Grant Mainstay "View" access to your Google Sheet(s).
   1. Copy the Mainstay Integrations user, integrations@mainstay.com, from this page.
   2. Within Google Sheets, click the "Share" button and then add this user with "Can View" access.
3. Back in Mainstay, click + New Integration.

   1. On the configuration screen, enter a Name and Description for your integration instance. This will appear in the list, but has no affect on the actual data sync. Note that you can enable or disable the integration at any time using the Enabled toggle.

      

   2. Enter the URL of your Google Sheet, including both the Sheet ID and the Tab ID. Mainstay will extract the relevant information from this URL. You can then click Test Access to verify that the information is entered correctly and the access was granted (in step 2.2 above).
   3. The Schedule is divided into 3-hour blocks. We recommend exporting contacts once a day, during off-hours (such as 12am - 3am). (Note that all times are in EST.)

   4. Set up your Data Mapping next. Mainstay requires that all data syncs include the following fields: CRM ID (your system's unique identifier), First Name, Last Name, and Phone Number. For each field, enter the exact column header that will contain this information. (For example, your system might call it "Mobile" or "cell_phone" instead of "Phone Number".)

      After mapping these required fields, click + Add mapping to specify additional columns and the equivalent Mainstay field. You can select both built-in Standard Fields and Custom Fields.

   5. When you're ready, toggle the integration to Enabled. (Note that you can return to this screen to update your data field mapping at any time in the future.)

Integrations List

1. After creating an integration instance, you can see a list of all inbound Google Sheets syncs.
2. Click the Enabled toggle to turn this sync on or off.
3. Click the ... overflow menu to see available actions:
   • 
   • Edit: Return to the configuration screen to make changes.
   • Delete: Remove this integration.

Troubleshooting

Here are some tips for resolving common issues with the Google Sheets sync.

Google Sheets Access

The first thing to make sure is that Mainstay has adequate access to your Google Sheet. For example, if you moved data to a different spreadsheet or a different tab, make sure to update the configuration so we're pulling from the right place. If you believe you have granted access, and the integration is enabled, but you're not seeing any updates in the Import History, contact Mainstay Support.

Invalid Formatting

Like the manual import process, our Google Sheets solution expects properly formatted spreadsheets. The first row will be used as headers, so make sure there are no extra empty rows before it, and make sure the header row isn't missing.

Missing Required Fields

It's not possible to enable an integration if the four required fields - CRM ID, First Name, Last Name, and Phone Number - are not mapped in your settings. However, it's definitely possible that your Google Sheet is accidentally omitting these columns! Pay special attention to uppercase/lowercase, leading/trailing spaces, extra quotes, dashes vs. underscores, and other subtle typos that may mess with your data mapping.

Blank Rows & Columns

Some external systems insert additional (blank) rows or columns, either before or after the main content. In some cases, the number of columns is not consistent for each row. (For example, "merged" cells can mean columns and rows don't line up.) Sometimes, an extra "header" row with metadata is inadvertently prepended to the actual content.

Row-level Validation

Assuming there are no issues with the file itself, it's very common for individual rows (ie, individual learners) to have validation issues for particular fields. For example, perhaps your file includes "Email", but some learners have an invalid value that does not conform to proper email address formatting. These issues will appear in the Import History tool (see below).

Import History

After a file from your external system is received and processed, an import report will appear in the Import History tool, just like a manual CSV upload.

You can filter this tool by Google Sheets:

Each Import History report will include a link to the original file, information on successful and skipped rows, and a downloadable list of validation errors. See the Import Error Dictionary for more information.