Integrating Patreon with Google Sheets

Friendly Help
1

Would really appreciate it if someone from the Patreon team could help me with this. Or anyone who knows where I am going wrong. I’ve poured over the API docs but there are some things I’m still really unclear about. The app has access but I still see a 401 error - so something is wrong here. Am I close? How do I get the names of pledges to a specific campaign so I can add the information you get from downloading the csv into my spreadsheet? I referenced this amazing article by Ben Collins where he does this with the Strava API.

var CLIENT_ID = 'HIDDEN';
var CLIENT_SECRET = 'HIDDEN';
var redirectURL = 'https://script.google.com/macros/d/HIDDEN:scriptID/usercallback'


// configure the service
function getPatreonService() {
  return OAuth2.createService('Patreon')
    .setAuthorizationBaseUrl('https://www.patreon.com/oauth2/authorize')
    .setTokenUrl('https://api.patreon.com/oauth2/token')
    .setClientId(CLIENT_ID)
    .setClientSecret(CLIENT_SECRET)
    .setCallbackFunction('authCallback')
    .setPropertyStore(PropertiesService.getUserProperties())
    .setScope('included:read_all');
}

// handle the callback
function authCallback(request) {
  var patreonService = getPatreonService();
  var isAuthorized = patreonService.handleCallback(request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Denied. You can close this tab');
  }
}

// custom menu
function onOpen() {
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Patreon App')
    .addItem('Get data', 'getPatreonPledgeData')
    .addToUi();
}

// Get pledge data
function getPatreonPledgeData() {
  
  // get the sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');

  // call the Patreon API to retrieve data
  var data = callPatreonAPI();
  
  // empty array to hold pledge data
  var patreonData = [];
    
  // loop over pledge data and add to patreonData array for Sheet
  data.forEach(function(included) {
    var arr = [];
    arr.push(
     pledge.first_name,
     pledge.full_name,
     pledge.email,
      pledge.last_name
    );
    patreonData.push(arr);
  });
  
  // paste the values into the Sheet
  sheet.getRange(sheet.getLastRow() + 1, 1, patreonData.length, patreonData[0].length).setValues(patreonData);
}

// call the Patreon API
function callPatreonAPI() {
  
  // set up the service
  var service = getPatreonService();
  
  if (service.hasAccess()) {
    Logger.log('App has access.');
    
    var endpoint = 'https://www.patreon.com/api/oauth2/api/campaigns/<HIDDEN:CampaignID>/pledges';
    var params = '?include=patron.null';

    var headers = {
      Authorization: 'Bearer ' + service.getAccessToken()
    };
    
    var options = {
      headers: headers,
      method : 'GET',
      muteHttpExceptions: true
    };
    
    var response = JSON.parse(UrlFetchApp.fetch(endpoint + params, options));
    
    return response;  
  }
  else {
    Logger.log("App has no access yet.");
    
    // open this url to gain authorization 
    var authorizationUrl = service.getAuthorizationUrl();
    
    Logger.log("Open the following URL and re-run the script: %s",
        authorizationUrl);
  }
}