How to receive data from a website in Google Sheets

How to receive data from a website in Google Sheets

Written on 2020-05-01

Do you find yourself having to open a website often and copy paste data from it to a sheet on Google sheets? Did you want a simpler way to extract data from a website when it changes, and send it to your sheet directly?

In the simplest of cases, 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.
  • You can only extract raw data

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.

1: Create a new Google sheet or configure an existing one

In order for Monitoro to connect to your sheet, you need to add a macro to your sheet.

  1. Make a copy of this template, and open the script editor by clicking on the top menu item "Tools" then "Script editor".
  2. Once you're in the script editor, press "Publish" in the top menu and then "Deploy as a web app".
  3. Press "Deploy", then copy the URL that appears.

If you already have an existing sheet, copy the code from the template into your sheet and apply steps 2 and 3.

2: Create a new service in Monitoro

  1. Go to services in Monitoro and create a new service.
  2. Choose Google sheet, paste the URL and name if it you want.

3: Create a monitor

  1. Open the website you want to monitor, for example a twitter account or your favorite online shop.
  2. Click on the Monitoro icon in the top right of your browser
  3. In the monitoro app, click "Add selection" to select parts of the webpage you want to extract when they change
  4. Click on "Connect service", and then select "Google Sheets".
  5. Add a cell reference in A1 notation, and then a value to fill in. You can use any data you selected from the page by wrapping its name like this {{name}}
  6. Click "Create monitor" and you're done! The monitor will send new data the next time it runs.

Do you have any questions? Hit the chat button in the bottom right and we'll jump in to help :-)

Want to stay up to date?
Sign up to our newsletter!
We'll never send you unsolicited email.