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 for Sheets. 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 CLOUD VISION 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=YOUR_API_KEY

Putting it together, we get the full API Request URL:

https://vision.googleapis.com/v1/images:annotate?key=AI....

Just substitute in your own API key.

PART 4: PULL GOOGLE CLOUD VISION API DATA INTO SHEETS

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-Typeapplication/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.
    {   "requests": [     {       "image": {         "source": {           "imageUri": "https://upload.wikimedia.org/wikipedia/commons/4/41/Sunflower_from_Silesia2.jpg"         }       },       "features": [         {           "type": "LABEL_DETECTION",           "maxResults": 10         }       ]     }   ] }
    google-cloud-vision-img16
  5. We don’t need any OAuth2 authentication so leave that as None.
  6. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  7. 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. You can read more about report styles here: Report Styles.
  8. Name your request and click Run. A moment later you’ll see an analysis of your image in Google Sheets:
    google-cloud-vision-img17

PART 5: GET MORE CLOUD VISION API DATA IN SHEETS

  • 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 ImgURLgoogle-cloud-vision-img18

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

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

{
  "requests": [
    {
      "image": {
        "source": {
          "imageUri": "+++ImgURL!A1+++"
        }
      },
      "features": [
        {
          "type": "LABEL_DETECTION",
          "maxResults": 5
        }
      ]
    },
    {
      "image": {
        "source": {
          "imageUri": "+++ImgURL!A2+++"
        }
      },
      "features": [
        {
          "type": "LABEL_DETECTION",
          "maxResults": 5
        }
      ]
    }
  ]
}

  • 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.

2 thoughts on “Import Google Cloud Vision API Data to Google Sheets”

  1. Could you reference where we could scan a google drive folder? Looking to analyze 1000+ images for text and looking how to do this.

    Reply
    • Hey Nik, the end of the article shows how to analyze multiple images in a single call. However, since Google requires that you use a different POST body for each image, it would be quite impractical to cycle through 1000 images at once, so I think you’d need something more custom here.

      Reply

Leave a Comment