Monday, January 18, 2021

Tasker and Google Sheets: Back On Speaking Terms

Combining Tasker and Google Sheets has on more than one occasion been a massive win. Recently the Tasker Spreadsheet Plugin stopped working and my attempt at using AutoWeb to access the Google Sheets API was a bust (I kept getting this exception). This left me with Plan C: use Tasker's built-in HTTP Actions to roll my own add-to-Google Sheets Task.

I braced myself for the hassle of implementing OAuth 2 using native Tasker actions. And then I learned about HTTP Auth and realized the heavy lifting was done. Building on the progress I made during my AutoWeb attempt, I was able to get my Task working almost too easily. I give you the: GSheet Append Row Task, a generic task that makes adding a row to a spreadsheet, dare I say, simple. Here's how you can use it.

Step 1. Setup

Import the GSheet Append Row Task into your Tasker.

Now comes the annoying part: you need to create a Google Cloud Project and OAuth 2 credentials in the Google Developer's Console. You can follow Step 1 from this tutorial to do this.

Ultimately, you're looking for two values: a Client ID and Client Secret. Once you have these values, edit the GSheet Append Row Task, and update the appropriate Set Variable actions at the top.

Once those settings are in place, you can close GSheet Append Row; if all goes well you won't need to edit it again.

Step 2. Call GSheet Append Row

You need to call GSheet Append Row with %par1 set to JSON with the following shape:

  ssid: '[spreadsheet ID - find this in the URL of the spreadsheet]',
  sheet: '[the name of the sheet you wish to append to]',
  row: [
    '[column 1 data]',
    '[column 2 data]',

You can prepare this JSON using the strategy outlined here. Though, I prefer to use a JavaScriplet:

  // the actions before this set:
  //  %ssid to the the spreadsheet ID
  //  %now to %DATE
  //  %url to the URL to learn more
  //  %description to the description of the entry
  // I'm looking to add a row with the format:
  //  date | description | url
  // Note: date is being changed from m-d-y to m/d/Y
  var args = JSON.stringify({
   ssid: ssid,
   sheet: "From the Web",
   row: [
     now.replace(/[-]/g, "/"),

  // %args is now ready to be passed as %par1.

Once %args has been prepared, it's trivial to invoke PerformTask, setting the task to GSheet Append Row and %args as %par1.

Here's a Sample Task to see this in action:

Step 3.

Celebrate! If all went well, the first time you run GSheet Append Row it will ask you for permission to run. See this tutorial for details about this first interaction. Once permissions has been provided, appending rows should Just Work.

In Conclusion

This turned out to be quite a bit easier than I imagined it (thanks João, you rock!). Though, I don't love the fact that HTTP Auth depends on João's auth.html. I'm tempted to make my own version of 'HTTP Auth' that works locally and doesn't require a 3rd party server. Still, for now I'm just happy to to back to writing Tasks that integrate with Google Sheets.

No comments:

Post a Comment