CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-api
Groundswell
Groundswell

Reputation: 11

How to use Google Civic API through multiple addresses using Google Sheets

I'm attempting to use Google's Civic API to run through a list of addresses to find the state representatives name and email address and place that data into a Google Sheet.

I've successfully made the call using one of the addresses, but is there a way to run the API call across multiple addresses and populate that into a new cell?

function findRep() {

  // Call the Google Civic API
  var response = UrlFetchApp.fetch("https://www.googleapis.com/civicinfo/v2/representatives?address={address}&includeOffices=true&levels=administrativeArea1&roles=legislatorLowerBody&roles=legislatorUpperBody&fields=divisions%2CnormalizedInput%2Cofficials&key={mykey}");
  Logger.log(response.getContentText());

  // Add to Google Sheet
  var fact = response.getContentText();
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 1,1).setValue([fact]);
}

Upvotes: 1

Views: 365

Answers (1)

Andres Duarte
Andres Duarte

Reputation: 3340

You can use the fetchAll() function [1] to send all the requests you need in batch. I get the addresses array from the "Addresses" sheet and insert the results in the same Spreadsheet but in the "Results" sheet.

function findRep() {

  var ss = SpreadsheetApp.openById("[SPREADSHEET-ID]");
  var targetSheet = ss.getSheetByName("Results")
  var sourceSheet = ss.getSheetByName("Addresses")
  var addresses = sourceSheet.getDataRange().getValues();
  var requests = [];

  //Loop to set up the requests array
  for(var i=1; i<addresses.length; i++) {
    var address = addresses[i];
    var request = 'https://www.googleapis.com/civicinfo/v2/representatives?address=' + address + '&includeOffices=true&levels=administrativeArea1&roles=legislatorLowerBody&roles=legislatorUpperBody&fields=divisions%2CnormalizedInput%2Cofficials&key=[API-KEY]';
    requests.push(request)    
  }

  //Get the responses in batch
  var responses = UrlFetchApp.fetchAll(requests);

  //Iterate the responses object and insert the data  
  for(var i=0; i<responses.length; i++) {
    var response = JSON.parse(responses[i].getContentText());
    var official = response.officials[0].name;
    targetSheet.getRange(targetSheet.getLastRow() + 1,1).setValue(official);
  }
}

In my code I only use the first value from the officials array and insert the name of that person. Because there's no email field for each official.

[1] https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app#fetchAll(Object)

Upvotes: 0

Related Questions