Google Sheets API Tips

Overview: You can now use the Google Sheets API to import and manage large amounts of data within our CMS. This integration streamlines data editing and ensures that data consistency is maintained through validation checks.

How It Works:

1. Starting the Import Process:
   • To begin, click the "Begin Import" button in the CMS. This will open Google Sheets in a new tab.
   • The email you used to log into the CMS will automatically be granted access to the Google Sheet.
2. Understanding the Spreadsheet Structure:
   • The spreadsheet contains individual sheets for each of our entities: Categories, Places, Stages, Performers, Events, Articles, FAQs, Banners, Partners, and Translations.
   • You can perform all your data editing directly within these sheets.
   • We recommend adding and editing your data in the order that the sheets appear: Categories, Places, Stages, Events, etc. This will allow for all of the data to be available to link the different types of data together. (e.g. adding a performer to an event or adding an event to a category)
3. Data Entry and Validation:
   • Important: We strongly recommend using the Paste Special function (Shift + Cmd + V) when pasting data into the sheet. This helps maintain the critical data validations embedded in the sheet.
   • These validations are essential for ensuring your data is correctly formatted. Data that does not meet these validations will not be imported. If just 1 piece of data is formatted incorrectly, no data will be imported until you resolve the formatting issue and import again. The faulty data will be clearly indicated in the spreadsheet.
   • If you accidentally remove any validations, don’t worry—there is an additional check when you hit the "Import Now" button in the CMS. This check will reapply the necessary validations to your data to ensure it is formatted correctly.
4. Handling Validation Errors:
   • If your data fails validation during the import process, you will be prompted to revisit your Google Sheet. The sheet will highlight the any cells that need correction.
5. Editing Data in the CMS
   • Important: We strongly recommend against editing data in the CMS while you have an active google sheet open. The google sheet acts as a snapshot of the CMS data at the time it is created (when the "Begin Import" button is pressed). This means that any edits made in the CMS will not update in the sheet if a sheet is created before the edits are made. In order to see the changes made to the CMS in the google sheet data, you must cancel your import and begin again.
   • Any edits made in the CMS while a sheet is open will be overwritten when the sheet is imported.
6. Finalizing the Import:
   • Once your data is validated and you successfully import it using the "Import Now" button in the CMS, your Google Sheet will be archived.
   • To edit your data again, click "Begin Import" in the CMS, and a new sheet will be created containing all current CMS data. This approach ensures the CMS remains the true source of data, avoiding potential confusion from simultaneous edits in the Google Sheet and CMS.

Key Features:

• Change Counter: A change counter in the CMS will track the number of pending changes in the Google Sheet. This serves as a reminder if you step away from the sheet or CMS before importing your changes.
• Automatic Archiving: If there are pending changes that remain untouched for 7 days, the sheet will be automatically archived.
• Successful Import Notification: If you're importing a large amount of data at one time, you will see a modal telling you your import is taking awhile. Once it's finished, another modal will pop up letting you know that the data was successfully imported.
• Cancel Import: If you make several changes but realize you've made mistakes, you can archive the sheet and start fresh by clicking the "Cancel Import" button in the CMS.
  • Note: The "Import Now" and "Cancel Import" buttons are only available if changes are detected in the sheet.

Conclusion: This integration with Google Sheets enhances your ability to manage large data sets with confidence, ensuring that all data imported into the CMS is validated and formatted correctly. For any issues or further assistance, please refer to our support documentation or contact the support team.