How to Integrate Microsoft Azure Cognitive Services with Google Sheets

Set Up Your Azure Cognitive Services

• Sign in to the Azure Portal.

 

• Navigate to "Create a resource" and select "AI + Machine Learning" and then "Cognitive Services."

 

• Fill in required fields such as subscription, resource group, region, etc., and create the resource.

 

• After deployment, navigate to your newly created resource and retrieve your API key and endpoint URL from the "Keys and Endpoint" section.

Set Up Your Google Sheets Environment

• Open Google Sheets and create a new sheet.

 

• Navigate to Extensions > Apps Script to open the Apps Script environment.

 

• In the script editor, you'll be writing code to call the Azure Cognitive Services API.

Create an Azure Cognitive Services API Request

• In Apps Script, write a function to make an HTTP POST request to the Azure Cognitive Services API using your endpoint and API key.

 

• Here is an example of a function that sends a request to the Text Analytics API for sentiment analysis:

function analyzeSentiment(text) {
    const url = 'YOUR_AZURE_ENDPOINT/text/analytics/v3.1/sentiment';
    const apiKey = 'YOUR_API_KEY';

    const options = {
        method: 'POST',
        headers: {
            'Ocp-Apim-Subscription-Key': apiKey,
            'Content-Type': 'application/json'
        },
        payload: JSON.stringify({
            documents: [{
                id: '1',
                language: 'en',
                text: text
            }]
        }),
        muteHttpExceptions: true
    };

    const response = UrlFetchApp.fetch(url, options);
    const jsonResponse = JSON.parse(response.getContentText());
    return jsonResponse.documents[0].sentiment;
}

Integrate API with Google Sheets

• Write another function to call analyzeSentiment() and get the sentiment for text in your Google Sheets.

 

• Here's an example of how to use the API on a specific cell range:

function analyzeSheet() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const range = sheet.getRange('A1:A10');
    const values = range.getValues();

    for (let i = 0; i < values.length; i++) {
        const sentiment = analyzeSentiment(values[i][0]);
        sheet.getRange(i + 1, 2).setValue(sentiment);
    }
}

Test and Deploy Your Script

• Save your script and return to your Google Sheets environment.

 

• Run the analyzeSheet() function from the Apps Script Editor to see the sentiment analysis appear next to your text data.

 

• If you encounter "authorization required" messages, grant the necessary permissions for the script to access your Google Sheets.

Use Azure Cognitive Services in Google Sheets

• Once integrated, you can use other Azure Cognitive Services APIs similarly by modifying the request payload and endpoint URL appropriate for the service you want to use.

 

• This integration can be expanded with more functionalities, such as triggering the script automatically with Google Sheets triggers like onEdit or onOpen.