Propublica API json data into Google Sheets

Question

This is my first post. I am a Google Sheets devotee using add-ons for anything complex. Sans add-on, I lurk (skulk) for snippets that sometimes work. Tired of illegitimate grifting I can't learn from and going back to basics.. I have been working on the below code for several days and after viewing videos and reading tutorials I am stuck.

Accessing: Propublica Campaign Finance API in Google Sheets to download one of several tables I intend to use. 2 functions in my code: getData and populateSheet. Logs show data each time. Sheet shows only the header row.

Here is my public spreadsheet:

https://docs.google.com/spreadsheets/d/11imT4T5wrvacZ0dRn-mjYA53EB5zsEvKFw4hcTcfJeg/edit?usp=sharing

Propublica Campaign Finance API instructions:

"The Campaign Finance API uses a RESTful style. The API only accepts GET requests. All requests begin with: https://api.propublica.org/campaign-finance/v1/. The API key must be included in all API requests to the server, set as a header: X-API-Key: PROPUBLICA_API_KEY

Here is the code I am using:

---

var URL = 'https://api.propublica.org/campaign-finance/v1/2018/committees/leadership.json'
var params = {
  headers: {'X-API-Key': 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'}
  }

function getData() {
    var response = UrlFetchApp.fetch(URL,params); //Fetch data from a given API URL
    var json = response.getContentText(); //Get data content responded from API URL. This is returned as text
    var data = JSON.parse(json); //Parse the text data as JSON
    Logger.log(data) //This will log the JSON response from API
    return data //Return JSON object recevied from API

}

function populateSheet() {
    var data = getData(); //Get JSON data from the above function
    var sheet = SpreadsheetApp.getActiveSheet();
    var lastRowCount = sheet.getLastRow();
    var header = ['id','relative_uri','name','address','city','state','zip','treasurer','party','fec_uri','candidate','leadership','super_pac','sponsor_name','designation','filing_frequency','committee_type','interest_group'];

    sheet.clear();
    sheet.getRange(1, 1, 1, header.length).setValues([header]);

    for (var i = 0; i < data.length; i++) {
        var row = [];
        var nextRow = sheet.getLastRow() + 1;
        row.push(data[i].id, data[i].relative_uri, data[i].name, data[i].address, data[i].city, data[i].state, data[i].zip, data[i].treasurer, data[i].party, data[i].fec_uri, data[i].candidate, data[i].leadership, data[i].super_pac, data[i].sponsor_name, data[i].designation, data[i].filing_frequency, data[i].committee_type, data[i].interest_group)
        sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);

    }
}

Thank you in advance for any guidance you may provide (even "go read this and learn")..

Answer 1

In order to pull all of the records rather than just 20 at a time, you need to use a recursive call at the end of your record set to call the entire process again, and get the next batch.

This code alters your API call by changing the URL's "offset" value, incrementing by 20 each time the function is re-run.

You need an "out clause" so that when it hits the last record and the data stream ends, it ends the process. That's found in this line here:

 if (!res || !res[0]) return

This has been tested and appears to be working to pull 566 records instead of just 20.

var params = {
  headers: {'X-API-Key': 'your key'}
  }

var offset = 0;

function getData(offset) {
    var URL = 'https://api.propublica.org/campaign-finance/v1/2018/committees/leadership.json?offset='+offset;
    var response = UrlFetchApp.fetch(URL,params); //Fetch data from a given API URL
    var json = response.getContentText(); //Get data content responded from API URL. This is returned as text
    var data = JSON.parse(json); //Parse the text data as JSON
    Logger.log(data) //This will log the JSON response from API
    populateSheet(data,offset);
//    return data //Return JSON object recevied from API
}

function populateSheet(data,offset) {

 // var data = getData(); //Get JSON data from the above function
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRowCount = sheet.getLastRow();
  var header = ['id','relative_uri','name','address','city','state','zip','treasurer','party','fec_uri','candidate','leadership','super_pac','sponsor_name','designation','filing_frequency','committee_type','interest_group'];
  sheet.clear();
  sheet.getRange(1, 1, 1, header.length).setValues([header]);


  // I added below.
  var res = [];
  var lenny = data.results.length;
  var counter = 0;
  data.results.forEach(function(e) {
    var temp = [];
    header.forEach(function(h) {
      temp.push(e[h]);
    });
    res.push(temp);
    counter++;
    Logger.log('counter is '+counter);
    if (counter == lenny) {
      Logger.log('GOT LENNY counter is '+counter);
      offset = offset + 20;
      getData(offset);
    }
  });

  var nextRow = sheet.getLastRow() + 1;
  if (!res || !res[0]) return
  sheet.getRange(nextRow, 1, res.length, res[0].length).setValues(res); // Modified

}