Skip to content
ScrapeField
Get a key

Blog · Guides ·

Google Maps places into a Google Sheet

A few lines of Apps Script add a menu to your sheet that fetches the places for a search and writes them in, a page at a time. Nothing to install, and the key stays out of the sheet.

ScrapeField

A list of businesses in a spreadsheet is where a lot of sales and research work starts. This guide adds a ScrapeField menu to a Google Sheet: type a search in a cell, choose the menu item, and the places come in as rows. It uses Apps Script, which every Google Sheet has, so there is nothing to install.

The key

Open Extensions → Apps Script. In the editor, open Project Settings, and under Script Properties add SCRAPEFIELD_KEY with your key as its value. Kept there, the key is not in the sheet, so sharing the sheet does not share the key.

The script

Replace the editor’s contents with this, and save:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('ScrapeField')
    .addItem('Fetch places for the search in B1', 'fetchPlaces')
    .addToUi();
}

function fetchPlaces() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const query = String(sheet.getRange('B1').getValue());
  const pages = Number(sheet.getRange('B2').getValue()) || 1;
  const key = PropertiesService.getScriptProperties().getProperty('SCRAPEFIELD_KEY');
  const rows = [['Name', 'Category', 'Rating', 'Reviews', 'Address', 'Phone', 'Website', 'Place ID']];
  let cursor = null;
  for (let page = 0; page < pages; page++) {
    let url = 'https://api.scrapefield.com/v1/google-maps/places?query=' + encodeURIComponent(query);
    if (cursor) url += '&cursor=' + encodeURIComponent(cursor);
    const res = UrlFetchApp.fetch(url, { headers: { Authorization: 'Bearer ' + key }, muteHttpExceptions: true });
    const body = JSON.parse(res.getContentText());
    if (body.error) throw new Error(body.error.message);
    for (const p of body.data) {
      rows.push([p.name, p.category, p.rating, p.user_ratings_total, p.formatted_address,
                 p.formatted_phone_number, p.website, p.place_id]);
    }
    cursor = body.meta.next_cursor;
    if (!cursor) break;
  }
  sheet.getRange(4, 1, sheet.getMaxRows() - 3, rows[0].length).clearContent();
  sheet.getRange(4, 1, rows.length, rows[0].length).setValues(rows);
}

Reload the sheet, and the ScrapeField menu appears. The first time you use it, Google asks you to allow the script to reach the internet and edit the sheet.

Using it

Put a search in B1, as you would type it into Google Maps, such as dentists in Porto, and how many pages you want in B2. Each page is up to 20 places and costs 3 credits: $2.34 per 1,000 pages on the smallest pack. The places land from row 4, with a header row.

A rating with no reviews is an empty cell, not a zero: we return null where Google shows nothing, and the sheet shows null as empty. Every field the script can read is in the places reference; add a column by adding it to both lists.

Why a menu, not a formula

A custom formula such as =PLACES(B1) would be shorter, but Sheets decides when a formula runs again: when its inputs change, and at times on its own. Every run is a call, and a call costs the same whether we answer it from our cache or not. A menu item runs only when you choose it, and the rows it writes stay as values.

The same script reads LinkedIn job searches or an Instagram account’s posts: change the URL and the columns. Every list pages the same way (how lists work).

More articles