How to sync data continuously from a website to Google Sheets

Do you often find yourself copy pasting data from websites to Google sheets, and wish you could automate that?

In the simplest of cases, you can use a built-in formula to extract data. However this approach is limited and doesn't scale to most real world needs. For those situations, Monitoro offers a complete solution that is more flexible and doesn't suffer from these limits.

This article will guide you through both approaches.

Using a built-in Google Sheets formula

You can use the IMPORTHTML formula function to extract data from a webpage directly into your sheet. An example to extract the 4th table from a wikipedia page looks like this:

=IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India","table",4)

This approach works great for simple situations, or when you need a limited amount of data in your spreadsheet. It has several limitations however:

• You can only extract one piece of data for each formula.
• There could only be a maximum of 50 formulas containing IMPORTHTML.
• This approach is only compatible with HTML websites. If something happens on the page after it's loaded for the first time, this formula won't pick it up (so javascript is out).

Once your needs outgrow IMPORTHTML, the way forward to extract more data with full flexibility is with Monitoro. Let's see how to do that.

Using Monitoro

Monitoro has a built-in Google Sheets integration that you can use to sync data with any of your sheets.

There are four different ways to sync monitored data from Monitoro to Google Sheets:

• Insert, which adds a new row every time the integration is triggered.
• Update, which updates an existing row.
• Upsert, which looks for an existing row and updates it, and if it couldn't find such a row, it adds a new one.
• Set, which updates a specific cell reference with the value of your choice.

All of them can be triggered from a project's integrations, using custom conditions.

Steps overview

1. Connect Google Sheets to Monitoro.
2. Select the spreadsheet, and the sheet your want to connect to Monitoro.
3. Configure your integration (inserting rows, updating ...).
4. Done!

1. Connect Google Sheets to Monitoro

To get started syncing data with Google Sheets, go to your Services page, create a new Service and select Google Sheets.

Open the services page here.

Click on "Sign in with Google" and name your integration accordingly.

‍

2. Sync data to Google Sheets

You can sync data to Google Sheets from any project in Monitoro to the spreadsheet of your choice by triggering it from an integration rule.

‍

3. Configure the integration

1. Choose one of the four actions we provide
2. If the action is Update or Upsert, add some fields to match the row to update (similar to how the VLOOKUP formula in Google Sheets works)
3. Add the fields you want to sync to Google Sheets
4. Save the integration by clicking on "Connect"

This example below uses the Upsert action, and updates rows in Google Sheets that contain the page link in column A, or adds new ones if not existing previously.

‍

Click on Connect and you're done! Your sheet will be updated automatically as Monitoro extracts new data.

Taking it further

At this point, you're able to hook any website to a spreadsheet on Google Sheets. Make sure to explore the 4 different actions we provide for Google Sheets.

To learn more about Monitoro, make sure to check out our blog.