Import Google Cloud Vision API Data to Google Sheets

Import Google Cloud Vision API Data to Google Sheets

Google’s Cloud Vision API uses machine learning and AI to automatically classify images and detect objects like faces and landmarks. In this guide, we’ll walk through how to use the Cloud Vision REST API directly from Google Sheets with just an API key, using the API Connector add-on. We’ll first set up our project, then get the API key from Google, and then set up a request to analyze images in our spreadsheet.

PART 1: SET UP YOUR GOOGLE CLOUD PROJECT

  1. While logged into your Google account, navigate to https://console.developers.google.com/ and click Select a Project.
    google-cloud-vision-img1
  2. A modal will appear, prompting you to choose an existing project or create a new one. You can do either, but here we’ll create a new one.
    google-cloud-vision-img2
  3. You’ll now see a screen asking you to name your project. We’ll call it ‘Cloud Vision’. Click Create.
    google-cloud-vision-img3
  4. Before we move on, you’ll need to set up billing. A billing account is required in order to use Cloud Vision API, but you won’t need to pay unless you use more than 1000 ‘units’ in a month. Pricing details are listed here, and billing can be enabled by following the instructions here. If this is your first time setting up billing on a project, you’ll likely be offered a free credit:
    google-cloud-vision-img13

PART 2: GET YOUR CLOUD VISION API KEY

  1. From the dashboard, click Enable APIs and Services.
    google-cloud-vision-img4
  2. Use the search box or scroll down the page to find ‘Cloud Vision API’. Click to select it.
    google-cloud-vision-img5
  3. On the Cloud Vision API details page, click Enable.
    google-cloud-vision-img6
  4. Back on the dashboard, choose Credentials from the menu.
    google-cloud-vision-img7
  5. Click +Create Credentials. We’re looking for an API key, but by default Google only prompts for OAuth Client ID and Service account credentials. So for now just click ‘Help me Choose’.
    google-cloud-vision-img8
  6. Click through and select ‘Yes, I’m using one or both’ when prompted about using this API with App Engine or Compute Engine.
    google-cloud-vision-img9
  7. This will bring you out to a page stating you don’t need any new credentials. Click Done.
    google-cloud-vision-img10
  8. You will now be able to access an API key as a credential. Select it from the dropdown menu.
    google-cloud-vision-img11
  9. Congrats, you have your Google Cloud Vision API key! Copy this and keep it safe, we’ll need it shortly. You can also restrict your key via this page, to prevent unauthorized use.
    google-cloud-vision-img12

PART 3: CREATE YOUR API REQUEST URL

We’ll follow the Google cloud platform documentation to run annotation and detection for an image.

  • API root: https://vision.googleapis.com
  • Endpoint: /v1/images:annotate
  • Query string: ?key={API key}
  • Query string example: ?key=AIzaTyEk2ElM8PMqKLktJVl-uVT3lm11tLys73c

Putting it together, we get the full API Request URL:
https://vision.googleapis.com/v1/images:annotate?key=AIzaTyEk2ElM8PMqKLktJVl-uVT3lm11tLys73c

Of course, you’ll need to substitute in your own API key (this one is just an example).

PART 4: ENTER YOUR VALUES INTO API CONNECTOR

We can now enter our values into API Connector and use the Google Vision API data in Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
  2. In the Create Request interface, choose the POST method and enter the Request URL we just created
    google-cloud-vision-img14
  3. Under Headers, enter a key-value pair like this:
    Content-Type application/json

    google-cloud-vision-img15

  4. We also need to create a request body identifying the image(s) we’ll be analyzing and what method we’ll be using. For this example, I chose an image of a sunflower from Wikipedia and selected the ‘label detection’ method. You can substitute in your own method and URL, or just paste in this request body as written.

    google-cloud-vision-img16

  5. Create a new tab. You can call it whatever you like, but here we’ll call it ‘VisionAPI’. While still in that tab, click ‘Set’ to use that tab as your data destination.
  6. Under Output Options, change the report style from ‘default (single row)’ to ‘compact’. For this API response, the output is more readable on separate rows.
  7. Name your request. Again we’ll call it ‘VisionAPI’
  8. Click Run and a moment later you’ll see an analysis of your image in Google Sheets:
    google-cloud-vision-img17

PART 5: NOTES AND EXPANSIONS

  1. If you’d like to analyze URLs more conveniently, you can base your POST body on the value in a cell (documentation). For example, here I’ve added the URL to cell A1 in the sheet ImgURL
    google-cloud-vision-img18

    I can now reference it by substituting in +++ImgURL!A1+++ instead of the full URL:
    google-cloud-vision-img19

  2. If you want to analyze multiple images in a single call, add them into the ‘requests’ array like this:

  3. Besides LABEL_DETECTION, you can try FACE_DETECTION, WEB_DETECTION, IMAGE_PROPERTIES, LANDMARK_DETECTION, and so on. Check the how-to guides for a full list of methods.
  4. IMPORTANT SECURITY NOTE

    Anyone with Owner or Edit access to your Google Sheet can view all the information you've saved within API Connector, including API keys and other credentials. Treat these keys as passwords and limit access to your sheet accordingly.

Comments:0

Leave a Reply

Your email address will not be published.