Google Sheets

How to set up a spreadsheet and connect it to Plecto.

Last updated: July 23, 2021

Setting up the spreadsheet

Please make sure that the data in your Google Sheets should include the following column headers: User IDUser nameExternal ID

In the following example from Google Sheets, the User ID is the employee ID (that could also be a phone number, e-mail or another unique number per employee), the User Name is the name of the employee and the External ID should be a unique number for each registration (e.g. row number). Value and Customer name are custom fields:

google-sheet.png

Example on how your Google Sheet could look like.

Date format on date columns in spreadsheet

It is very important the date format is correct in your spreadsheet. 

  1. Click 'File' > 'Spreadsheet settings'
  2. Select "Denmark".
  3. Mark your date column and click 'Format' > 'More date and time formats'
  4. Make sure to select YYYY-MM-DD
google-sheet-2.png
google-sheet-3.png

This affects formatting details such as functions, dates, and currency.

Setting up in Plecto

Important: Before you begin, you'll need to create a data source in Plecto:

  • Go to 'Data management' > 'Data sources' > 'New data source'.
  • Select 'Custom Input' under 'Manual data entry'.
  • Write what kind of data you want to collect (e.g. Sales, Leads, Budgets).
  • Design the registration box (optional). Here you can add fields to the registration box. You can add as many as you like - *You need the same fields as what you have in your excel/Google sheet*
  • Click 'Save'.
google-sheet-4.png

Example on how to set up and design the data source in Plecto.

After creating the data source in Plecto, simply go to 'New data source' in Plecto.

  • Select Zapier under 'Other'.
  • Select Google Sheets in the list of zaps by clicking on 'Use this zap'.

Setting up in Zapier

Select your Trigger:

  • Select your trigger: Google Sheets.
google-sheet-5.png
  • Select New or Updated Spreadsheet Row.
  • Connect your Google Account.
  • Set up the spreadsheet: 
google-sheet.png

Select your Action:

  • Select Plecto > 'Create a new registration'.
  • Connect your Plecto account.
  • Edit the template

Please make sure that the template is filled out correctly:

  • Integration Name: Make sure to always write it the same way, if you use multiple zaps from the same 3rd party system, as we use the name to group the users together.
  • User ID: Employee ID, e-mail or a unique name/number of each employee.
  • User Name: The name of the employee.
  • External ID: A unique number for each registration, e.g. row number/ID.
  • You might have to click 'Refresh Fields' to show the custom field(s) you have created in your API data source in Plecto. 

How to import already existing data

Zapier is generally not designed to mass import/export historical data, but there are some methods to get around this limitation. Follow this guide on how to import your already existing data:How to import/export already existing data from Zapier to Plecto

Important: Zapier Formatter

Please make sure to use the Formatter in Zapier. You will find it in between the Trigger and Action. Click on the + and select the formatter:

google-sheet-7.png

Make sure the format is the same as in the spreadsheet: YYYY-MM-DD.

Was this article helpful?