Beeminder Forum

Google Apps Script to import goals into spreadsheet

Just wrote some App Script code to import my Beeminder goals into a spreadsheet. To use:

  1. Open a sheet in a Google Sheets spreadsheet you want to overwrite (it will overwrite!).
  2. Click “Tools > Script editor”
  3. Paste in the below code.
  4. On line 3 replace placeholders with your username and auth token.
  5. Click the run button in the toolbar.
  6. If asked, grant the requested permissions. The permissions requested allow the script to query Beeminder’s API and write to your spreadsheet.

That’s it!

function importGoals() {
  Logger.log('hello world')
  var response = UrlFetchApp.fetch('https://www.beeminder.com/api/v1/users/YOUR_USER_HERE/goals.json?auth_token=YOUR_TOKEN_HERE')
  //Logger.log(response)
  var goals = JSON.parse(response)
  var headers = Object.keys(goals[0])
  var data = [headers]
  
  goals.forEach(function(g) {
    //Logger.log(g.slug)
    data.push(Object.values(g))
  })
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(1,1,data.length,headers.length)
  
  //Logger.log(data)
  range.setValues(data)
}
3 Likes

You can get real fancy with this!

I’ve been tracking my safety buffer multiple times a day so I have some historical information about “pressure”, and it grew from there. (Grew to the point where I had to archive my historical information into different sheets because I hit the total sheet row limit a few times, even…)

3 Likes

Oh, neat… How did you make the update button?

1 Like

You can add a menu dropdown like this: https://developers.google.com/apps-script/quickstart/custom-functions (which I didn’t show…)

and you can add clickable buttons like this: https://www.benlcollins.com/apps-script/google-sheets-button/

2 Likes

Wow… I wonder if you could turn this into a Google Sheets template? Just have username and API key be in an options sheet in the same file? :thinking:

1 Like

I am not sure what the best ways are to distribute Google Sheets things.

I have played around with using the different cache variables in Google App Script to make light-weight redistributable scripts. It works ok, but it didn’t work well enough that I’d suggest it as a first choice without looking at the other “built-in” options.

2 Likes