Quartzy users can now sync data from their Quartzy lab to Google Sheets for easy tracking, sharing between teams, managing multiple labs data in one place, and more.
Some common use-cases are:
- Tracking lab expenses
- Tracking order deliveries
-
Inventory tracking and auditing
- Data backup (sync deleted items to Sheets)
- Managing multiple labs
Read below to learn how to utilize webhooks and Zapier, a workflow automator, to configure this integration for your lab!
Requirements:
- Google Sheets
- Quartzy: Lab Admin role
- Zapier: Professional, Team, or Company subscription plan
How to configure your Google Sheets sync from Quartzy
1. Set up your Google Sheet
To get started, download the following file, then import it to Google Sheets: Quartzy > Google Sheets Template
There are tabs at the bottom for Requests tracking and Inventory tracking. Navigate to the tab for the type of tracking you'd like to configure.
The column headers represent the data for each request that can sync from Quartzy.
If there's any data which your lab does not want to track on your Google Sheet, simply delete the relevant column(s). You can also rearrange the order of the columns to fit your needs.
2. Configure your webhook
Next, login to your Zapier account. Once logged in, click on the template link below for the type of tracking you're configuring.
You'll be taken to the following page:
Click Try this Zap to open the Zap editor. You'll see a page like below:
Click Continue for both the Setup and Configure steps.
On the Test step, Copy the URL under "Your webhook URL" to your clipboard or notes app.
Open a new tab and log into Quartzy. Locate the lab you want to integrate with Google Sheets, and navigate to Manage Labs > Developers. Click the Add webhook button.
Enter a name for your webhook (something like "Requests > Google Sheets" or "Inventory > Google Sheets" is recommended), paste the URL you copied from Zapier into the URL field, and select the Event Types relevant to your use-case (see below):
Requests tracking:
- Order Request Created
- Order Request Deleted
- Order Request Ordered
- Order Request Received
Inventory tracking:
- Inventory Item Created
- Inventory Item Updated
- Inventory Item Deleted
Select the checkboxes next to Flatten Output? and Set null values to an Empty String? Click Save.
For testing purposes, create a fake request or inventory item.
Back in Zapier, click the Test Trigger button.
Note: it can take up to two minutes for data to be returned. If your first test fails, please wait 1-2 minutes and then click Test Trigger again.
Once a request populates on your page, select it and then click Continue with selected record.
Tip: if testing the trigger fails, make sure you created your fake request or inventory item in the correct lab.
3. Connect your Google Sheets account and configure the "Lookup Spreadsheet Row" step
If you don't already have your Google Sheets account connected to Zapier, follow Zapier's instructions to connect accounts.
Click Continue, then select the appropriate Google Drive, Spreadsheet, and Worksheet (either the Requests or Inventory tracking sheet).
Select the following for the Lookup Column field:
- Requests: select Request ID
- Inventory: select Quartzy Serial Number
Lookup Value field: click the + icon, then search for and select the following:
- Requests: Data Order Request ID
- Inventory: Data Inventory Item Serial Number
Leave Supporting Lookup Column, Supporting Lookup Value, and Search from Last Row fields blank.
Click the checkbox for Create Google Sheets Spreadsheet Row if it doesn't exist yet?
The column headers from your Google Sheet will show up as fields beneath that checkbox. Use the + icon in each field to select the appropriate dynamic data (ex: for the Item Name field, select Data Order Request Item Name, for Catalog # select Data Order Request Catalog Number, etc).
Notes:
- For Requests tracking, the "Total Price" will need to be formatted to show up as dollars and cents. Enter a "=" before the data and then "/100" after like below:
- The Budget field's data is called Data Order Request Spend Tracking Code:
- If you'd like timestamps to show just the date instead of the full timestamp, you can format the field as seen below:
Once you have all fields populated, click Continue.
Click the Test step button, then check your Google Sheet - there should be a newly created row with your request (or inventory item) details. Click Continue back in Zapier.
4. Configure the Update Spreadsheet Row step
Click Continue on the Setup page. On the Configure page, select the same Drive, Spreadsheet, and Worksheet as before. For the Row field, there should only be one option - select this option.
Then, populate each field in the same way as you did for the previous step, using the + icon to enter the appropriate data for each field.
Click Continue, and then click Test step.
Next, click the Edit icon, and edit the quantity of your Request or Inventory item to a different number (click on the current value to edit it).
Once you've edited the quantity, click Stop Editing, then click Retest step.
The row on your Google Sheet should be updated with the new quantity. If it's updated successfully, click the Publish button.
That's it! Going forward, your Google Sheet will be populated with Requests or Inventory items for easy tracking.
If you have any questions or encounter any issues, please reach out to support@quartzy.com for assistance.
Comments