Webhooks example: Create a sign in/out report with Google Sheets

In this example, we’ve created a spreadsheet that summarizes the sign in/out detail of employees in a location, pulling this information in real-time from MRI OnLocation.

How it works

This integration is run using webhooks created in Zapier, which connects a Google Sheets spreadsheet with your OnLocation account.

By setting up triggers in OnLocation, each time an employee signs in to your location, their details are added as a new row in the spreadsheet. The same row is then instantly updated when the employee signs out.

This may be a good solution for your organization if you need a report of who is currently on-site and you don’t want to give OnLocation reporting access to everyone who needs this information.

Google-Sheets-Example.png

Before you start

  • Create your spreadsheet in Google Sheets. Make sure you include all the fields you need to match with your OnLocation account, for example System ID, First Name, Surname, Signed In, Signed Out.
  • Sign up for a paid Zapier account (if you don’t already have one)
  • Make sure you have either the Account Owner or IT Support user role in OnLocation
To avoid connection errors, you must set up the integration in this sequence. 

Set up the integration

Step 1: Create a webhook in Zapier

Create a webhook in Zapier. This webhook will be used to record employee sign in information in your spreadsheet. 
  1. Log into your Zapier account.
  2. Click Make a Zap.
  3. Select Webhooks by Zapier as your Trigger App.
  4. Select Catch Hook, then click Continue.

    Zapier-1.png
  5. Click Copy next to the Custom Webhook URL, then click Continue.

    Zapier-Copy.png

Keep this tab or window open, you'll continue working on this screen in step 4. 

Step 2: Add the webhook in OnLocation

To add the webhook:

  1. In a new tab, log in to OnLocation.
  2. Go to Tools > Accounts.
  3. Select Integrations from the left-hand menu.
  4. Click the Webhooks tile.
  5. If this integration isn't active, click Enable, then click Manage Settings. If you're already using Webhooks, click Settings.
  6. Click Create a New Webhook.
  7. Add the Webhook Name. You may like to note it is for Google Sheets employee sign in.
  8. Paste in the Webhook URL from Zapier.
  9. Select POST as the method.
  10. Select Form encoded as the content type. 
  11. Click Save.

Step 3: Create a trigger in OnLocation

Set up a trigger so OnLocation connects an event to the webhook:

  1. Go to Locations, then select the location from the drop-down list.
  2. Select Triggers.
  3. Click Create a New Trigger.
  4. Enter a trigger name. You may like to note it is for Google Sheets employee sign in.
  5. Set the trigger status to Active.
  6. Select Employee Sign In as the trigger event, then click Next.
  7. (Optional) Select the Trigger Rules tab, enter any trigger rules that you might like to use, then click Next.
  8. Select the Trigger Actions tab, then click Create a New Action.
  9. Select Webhook – Triggers a Webhook URL from the drop-down menu.
  10. Select the webhook. 
  11. Select Full as the data type.
  12. Click Preview.
  13. Select a record, then click Send Data.
  14. Click Save & Close.

Keep this tab or window open, you'll continue working on this screen in step 6. 

Step 4: Create an action in Zapier

Create an action in Zapier to send the employee sign in information to the spreadsheet. Open Zapier in the same tab or window you were using previously:

  1. Click Test trigger. If it's successful, click Continue.
    If it fails, check that you've entered the custom URL correctly in OnLocation and try the test again.
  2. Select Google Sheets as the app.
  3. Select Create Spreadsheet Row as the event action, then click Continue.

    Step_4_-_Create_Action.png
  4. Select your Google Sheets account, click Continue.
  5. Customize the spreadsheet row: choose the drive, select the spreadsheet and worksheet, then insert the relevant data fields. In this example ID, Firstname, Lastname and Signed In have been added. 

    Step_4_-_Customize_Row.png
  6. Click Continue. 
  7. Click Test & Review.

To check that the first stage of the integration works, sign an employee into your location using your Sign In/Out Manager, then look for the update in your spreadsheet.

Spreadsheet-_example_1.png

Step 5: Create a second webhook in Zapier

Create another webhook in Zapier. This webhook is to update the employee sign out information in the spreadsheet. 

  1. Click Make a Zap.
  2. Select Webhooks by Zapier as your Trigger App.
  3. Select Catch Hook.

    Zapier-1.png
  4. Click Continue.
  5. Click Copy next to the Custom Webhook URL, then click Continue.

    Zapier-Copy.png

Keep this tab or window open, you'll continue working on this screen in step 8.

Step 6: Add the webhook in OnLocation

To add the webhook:

  1. Go to Tools > Accounts.
  2. Select Integrations from the left-hand menu.
  3. Select the Webhooks integration, click Settings.
  4. Click Manage Settings.
  5. Click Create a New Webhook.
  6. Add the Webhook Name. You may like to note it is for employee sign out.
  7. Enter the Webhook URL (paste this from your copied URL in Zapier).
  8. Select POST as the method.
  9. Select Form encoded as the content type.
  10. Click Save.

Step 7: Create a second trigger in OnLocation

Set up a trigger for employee sign out:

  1. Go to Locations, then select the location from the drop-down list.
  2. Select Triggers.
  3. Click Create a New Trigger.
  4. Enter a trigger name. You may like to note it is for Google Sheets employee sign out.
  5. Set the trigger status to Active.
  6. Select Employee Sign Out as the trigger event.
  7. (Optional) Select the Trigger Rules tab, enter any trigger rules that you might like to use, then click Next.
  8. Select the Trigger Actions tab, then click Create a New Action.
  9. Select Webhook – Triggers a Webhook URL from the drop-down menu.
  10. Select the second webhook. 
  11. Select Full as the data type.
  12. Click Preview.
  13. Select a record, then click Send Data.
  14. Click Save & Close.

Step 8: Create an action in Zapier

The last step is to create an action in Zapier to update the spreadsheet with the employee sign out details:

  1. Click Test trigger.
  2. Select Google Sheets as the app.
  3. Select Update Spreadsheet Row as the event action, then click Continue.

    Zapier-3.png
  4. Select your Google Sheets account, then click Continue.
  5. Customize the spreadsheet row: choose the drive, select the spreadsheet, and worksheet.
  6. Click on the Row field, then click Add a search step. A new action will appear above the one you've just added. 

    Zapier-4.png
  7. Select Google Sheets as the app and Lookup Spreadsheet Row as the event action, then click Continue.

    Zapier-5.png
  8. Select your Google Sheets account, then click Continue.
  9. Customize the spreadsheet row: choose the drive, select the spreadsheet, and worksheet.
  10. Choose ID as the Lookup Column.
  11. Choose ID as the Lookup Value.

    Zapier-6.png
  12. Click Continue.
  13. Click Test & Review.
  14. Go back to the Update Spreadsheet Row action, then add the Signed Out field to the relevant data category. In this example, it has been added to the Time Out column in the spreadsheet. 

    Zapier-7.png
  15. Click Continue. 
  16. Click Test & Review.

To check the integration, sign out an employee in your Sign In/out Manager, and check that the row updated in your spreadsheet.

Spreadsheet-_example_2.png