Data from Patreon into Google Sheets

Question

I'm trying to pipe data from a specific Patreon Creator Page into a Google Spreadsheet such as: Member first name, member last name, tier, etc. etc.

I've read the Patreon Developers Documentation here. https://docs.patreon.com

I referenced Ben Collin's tutorial on integrating Google Sheets with the Strava API here: https://www.benlcollins.com/spreadsheets/strava-api-with-google-sheets/

I've successfully connected Patreon to my Google App and have granted access but still no data. I am getting the following error when I run the script:

TypeError: Cannot find function forEach in object [object Object].

Any ideas as to how I can get the data I'm after?

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(pledge) {
    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);
  }
}

score 1 · Answer 1 · answered Sep 17 '19 at 12:28

The error comes because the Oauth flow is not being completely executed (It may be that it's not presenting the consent screen, you're not logging in with your account, etc).

As stated in the Oauth2 library documentation for Apps Script that you're using [1] in the "Connecting to a Google API" part, a more simple way to obtain the access token is using ScriptApp.getOAuthToken() function [2] instead of service.getAccessToken(), and set the needed scopes in the manifest file [3].

In any case, I hardly believe that could give you access to the Patreon API [4]. Also, I don't see the url you're using in the Patreon API documentation [5].

[1] https://github.com/gsuitedevs/apps-script-oauth2

[2] https://developers.google.com/apps-script/reference/script/script-app#getoauthtoken

[3] https://developers.google.com/apps-script/concepts/manifests#editing_a_manifest

[4] https://docs.patreon.com/#oauth

[5] https://docs.patreon.com/?javascript#api-endpoints

Thank you Andres. I'm totally new to both Apps Script and connecting APIs to Sheets and thought I would cut my teeth on this but it might be way over my head. Will keep learning and try to take a whack at this later. The URL I used was not in the docs, but the gsuite example required a URL so I improvised. — Polemistis D'Gnosis, Sep 17 '19 at 14:59

Data from Patreon into Google Sheets

1 Answers1