Connect Google Sheets to Plecto With Zapier

A guide on how to connect your Google Sheets to Plecto with Zapier.

Last updated: May 4, 2022

To connect your Google Sheets spreadsheets to Plecto with Zapier, you need do the following steps:

  1. Create a manual data source in Plecto.
  2. Prepare your Google Sheets spreadsheet by adding 3 mandatory fields.
  3. Configure a Zap in Zapier.

Read below for more detailed instructions on each of the steps.

First, you need to create a manual data source in Plecto before you can connect it to your Google Sheets spreadsheet. To add a manual data source, follow the steps below. For more information on manual data sources, read this article.

  1. Go to Data management > Data sources > + New data source.
  2. Scroll to the bottom of the page and select Custom input under Manual data entry.
  3. Give your data source a title, add teams (optional), then click Save.
  4. Click + Add field to add custom fields to your data source. You should add the same number of fields as you plan to import from your Google Sheets spreadsheet. If your spreadsheet has a field called Value, make sure to add a field with the same name to your data source in Plecto. This way, Plecto will know how to sort your data.

Once all the fields are added, your data source is ready.

Google Sheets Custom Fields DS.png

An example of the default and custom fields in a data source. The custom field names are displayed in bold.

Here's an example of how to add more fields to your manual data source.

Both Plecto and Zapier need to be able to recognize and sort the data from your spreadsheet. To ensure that everything works properly, you need to add three mandatory fields in your spreadsheet:

  • User ID: Each line must provide information about an employee so that Plecto understands who's responsible for the registration. The User ID could be a phone number, email, or another alphanumerical chain that is unique for each employee. You will see this ID number in the employee settings as External ID under External Accounts in Plecto.
  • User name: This will show in the Employee field in your Plecto data source.
  • External ID: This should be a unique number for each registration. It will show in the ID field in your Plecto data source. The External ID is a smart and easy way to update your data. Keep the same External ID, and once you update the spreadsheet, Plecto will update the registrations with the same External ID instead of creating new ones.

Having these fields will make it significantly easier to configure the Action in Zapier.

Google Sheets Example Spreadsheet.png

An example of a Google Sheets spreadsheet that also includes the mandatory fields: User ID, User name, External ID.

Calculated fields are imported as static values

This integration works best with simple spreadsheets that don't contain calculations and formatting (except for date formatting, read below). The data in your spreadsheet should be as raw as possible to imitate a data source in Plecto. Once Plecto has your data, you can start calculating your metrics by building formulas in the formula editor.

Date format in your spreadsheet

Each registration that gets imported to Plecto needs a valid date. Therefore, make sure to include a date format in your Google Sheets that Plecto can recognize, for example, 5/22/2021 or 2021-05-22.

You can also use the Date format in your Google Sheets:

  1. Select the entire column that has date values in your Google Sheets spreadsheet.
  2. Go to Format > Number > More Formats > More date and time formats.
  3. Select 1930-08-05 (represents the YYYY-MM-DD format), then click Apply.
Date Format Google Sheets 2.png

An example of the date format you should select for the date fields in your Google Sheets spreadsheet.

You have the data source and spreadsheet ready. Now you only need to create a Zap in Zapier. Use our integration to make it as easy as possible.

  1. Go to Data management > Data sources > + New data sourceZapier.
  2. Find Google Sheets in the list and click Use this Zap. You might have to log in to your Zapier account to continue and configure the Zap.
Zapier New Data Source.png

An example of a list of Zaps that you can use to connect your system to Plecto.

Step 3.1 : Set up the Trigger

A trigger is the causing event that will prompt Zapier to create a registration in Plecto.

  1. Choose app & event: Select Google Sheets. As for the trigger event, you can select from a list of triggers. For this example, we have selected New or Updated Spreadsheet Row.
  2. Choose account: Select the Google account that is responsible for the spreadsheet.
Zapier Trigger Google Sheets 1.png

An example of the Choose account settings in the Trigger window in Zap editor.

3. Set up trigger: This is where you select the spreadsheet, worksheet (data sheet within the spreadsheet), and column that will trigger Zapier to create a registration in Plecto.

Zapier Trigger Google Sheets 2.png

An example of the Set up trigger window in Zap editor.

4. Test trigger: Test your trigger to see if Zapier can find data in your Google Sheets spreadsheet. If the trigger test was successful, you will see a confirmation message "We found a spreadsheet row."

Zapier Trigger Google Sheets 5.png

An example of a successful trigger test message.

Step 3.2 : Set up the Action

If you added the mandatory fields (User ID, User name, External ID) and named other fields in your Plecto data source exactly as they are named in the spreadsheet, this part will be very easy to set up.

  1. Choose app & event: Choose Plecto and Create New Registration.
  2. Choose account: Choose your Plecto organization (the organization where you created the manual data source).
  3. Set up action
    1. Integration Name: Write down a name that describes what you're connecting to Plecto. For example, Zapier or Google Sheets. If you create multiple Zaps, make sure to always write the integration name the same way to ensure consistency. You will be able to see the integration name on the employee page under Data suppliers.
    2. User ID: Select the User ID field from the drop-down list.
    3. User Name: Select the User Name field from the drop-down list.
    4. Data Source: Select the manual data source you created for this Google Sheets integration.
    5. External ID: Select the External ID field from the drop-down list.
      If you have additional fields in your data source, select the corresponding fields from your spreadsheet from the drop-down list. To proceed to the action test, click Continue.
Zapier Action Google Sheets 6.png

An example of the Set up action settings in the Action window in Zap editor.

4. Test action: To test the action, click Test & Continue. If the test is successful, you will see a confirmation message. To finish the Zapier setup, click the Turn on Zap button.

Zapier Action Google Sheets 7.png

An example of a successful action test.

Do you already have some data you want to transfer from Google Sheets to Plecto? You can make Zapier do the work for you. Here's what to do:

  1. Go to Zapier > Zaps and find the Zap that connects Google Sheets to Plecto.
  2. Toggle the downward arrow on the right side of the Zap.
  3. Click the Transfer Existing Data button. A new tab will open.
Zapier Transfer Existing Data.png

An example of where to find the Transfer Existing Data button in Zapier.

4. From there, select the registrations (in Zapier, records) you want to send to your Zap, then click Send data to Zap. Zapier will now run your data and create registrations in Plecto.

Zapier Transfer Existing Data 3.png

An example of the Zapier Transfer page that allows sending existing Google Sheets data to your Zap.

Automate Plecto with Zapier

Learn how to create lunchtime and birthday reminders in Plecto by connecting it to your Google, Outlook, or other calendars with Zapier.

Was this article helpful?