Google Sheets

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

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:

Zapier test

Example on how your Google Sheet could look like.

 

Important note: 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

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


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. 
  • Click 'Save'.

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'.


In Zapier:

Select your Trigger:

  • Select your trigger: Google Sheets.

  • Select New or Updated Spreadsheet Row.
  • Connect your Google Account.
  • Set up the spreadsheet: 


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:

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