Add a row of data to a Google Spreadsheet (no plugins) 2019-07-08

Tasker allows you to use most Google APIs using its HTTP Auth and Request actions

  1. joaomgcd
    Since Tasker can now use OAuth 2.0 to authenticate users on APIs, a whole world of possibilities opens up with Web APIs.

    In this example, lets see how we can authenticate with the Google Sheets API and use it to add a row to an existing Google Sheet, like so:


    Important Note: The included download link has a more robust version of the tasks below, so it's slightly different. Check out the Sheet Test task in the project for examples on how to use.

    STEP 1 - CREATE CLOUD PROJECT


    /!\ If you already have a Google Cloud Project you can skip to step 2


    STEP 2 - ENABLE GOOGLE SHEETS API


    • Press the menu button on the top left
    • Select APIs and Services > Dashboard
    • At the top select your project from Step 1 (or any existing project you may want to use instead)
    • Select ENABLE APIS AND SERVICES
    • Search for Sheets
    • Enable the Google Sheets API


    STEP 3 - CREATE CREDENTIALS


    • Press the menu button on the top left
    • Select APIs and Services > Credentials
    • Click Create credentials > OAuth client ID
    • If needed create your consent screen as shown in the video. Make sure to add joaoapps.com as an authorized domain
    /!\ Important: since this tutorial was created some things have changed. In the OAuth consent screen options:
    • You need to add the scopes mentioned in step 5
    • You need to make the user type External
    • You need to add your own email address as a Test User
    /!\ If you don't do the additional steps above you might not be able to authenticate. Now on with the regular tutorial...
    • Back in the Create OAuth client ID screen select the Application Type: Web Application
    • Name it anything you want
    • Add an authorized redirect URI: https://tasker.joaoapps.com/auth.html
    • Click on Create
    • Take note of your Client ID and Client Secret


    STEP 4 - START AUTH PROCESS


    • In Tasker create a new task and call it Auth
    • Add an HTTP Auth action
    • Set the Client ID to the one you got before
    • Set the Client Secret to the one you got before
    • Set the Endpoint To Get Code to https://accounts.google.com/o/oauth2/v2/auth (this is the always this value for all Google Web APIs)
    • Set the Endpoint To Get Refresh Token to https://www.googleapis.com/oauth2/v4/token (this is the always this value for all Google Web APIs)
    • We now need the scopes for the specific API we want to access: Google Sheets. Let's find out what these are...


    STEP 5 - GET GOOGLE SHEETS SCOPE


    • Go to https://developers.google.com/oauthplayground/
    • Use Ctrl+F to find the word Sheets on the page
    • Expand the Google Sheets API v4 item
    • Check that the scope needed is https://www.googleapis.com/auth/spreadsheets by hovering over the various available scopes. This is the one that allows you to write sheets (we need that to add values to sheets) but doesn't give additional privileges like the ability to read any file from your Google Drive
    • Copy- this scope on to your Android device


    STEP 6 - FINISH AUTH PROCESS


    • Paste the scope you got from the previous step
    • Go back and add a Flash action to show %http_auth_headers
    • Run the task. The auth process should now run and the needed HTTP headers should be shown when your done
    • Add a Return action to the task and make it return the %http_auth_headers value. This will make this task be easily usable by any other task to call Google Sheets in an authenticated way


    STEP 7 - TEST AUTHENTICATION HEADERS


    (i) Let's see if calling this from another task is working correctly

    • Go back to the Tasker main screen and apply your settings with the tick icon
    • Create a new task: Append
    • Add a Perform Task action
    • Set Name to Auth
    • Set Return Value Variable to %headers
    (i) This will get the value that was used in the Return action above and set it to a variable in this task
    • Use a Flash action to show the value of %headers and check that it is set
    /!\Make sure to disable the Flash action from the Auth task, just to make sure that you're looking at the correct flash here :p
    • Run the Append task and check that the headers are shown in the toast


    STEP 8 - DISCOVER THE GOOGLE SHEETS API APPEND FUNCTION


    (i) Edit the spreadsheet and look at its URL. Something like https://docs.google.com/spreadsheets/d/1mYXuVltboFQ9RDfzHrgrxHY2mkUgyY4SQ23-gvZmHCs/edit#gid=0 The part betweeb d/ and /edit is the spreadsheet ID. In this case it would be 1mYXuVltboFQ9RDfzHrgrxHY2mkUgyY4SQ23-gvZmHCs
    • Use the ID in the test screen
    • Use the range A1:Z1000.
    (i) This will make the Sheets API look in that whole range for a table, and append the data at the end of it.
    • Set the valueInputOption to RAW (or to USER_ENTERED if you prefer the Sheets API to interpret numbers, etc)
    • Build the request body using the helper on the web page.
    • Set majorDimension to ROWS
    • Set range to same value as above: A1:Z1000
    • Set values to [["A","B","C"]]
    • Click on Execute and accept permissions
    • Check that the API was executed and that the row was inserted in the spreadsheet
    • In Chrome open Menu > More tools > Developer Tools > Network tab
    • Select XHR at the top
    • Execute the API action again and check the request that was made and its various properties.
    (i) We can now simply check what the browser called and mimic that in Tasker to make the API work!


    STEP 9 - ADD ROW TO SHEET FROM TASKER


    • Add an HTTP Request action
    • Set the Headers field to %headers
    • Copy the values from the API Console request:
    • Run the action. It should now add a row with A,B,C to your sheet!


    STEP 10 - ADD VALUES TO SHEET FROM A TASKER ARRAY


    (i) To make it easy to use this task from Tasker you have to modify it a bit so that you can directly insert data from a Tasker array. The values need double quotes around them to be used in the API, so lets add those.

    • Create an example array with the Array Set action: %values array with 1,2,3 as items
    • Add a For action and set Variable to %value and Items to %values() (/!\ wrongly inserted as %values in the video at first)
    • Add an End For action
    • Add an Array Push action and set
      • Variable Array: %final
      • Position: 9999
      • Value: "%value" (with double-quotes)
    • Put this Array Push action inside the For
    • Replace "A","B","C" with %final() in the HTTP Request action's Body field, so that the values from the variable are used.
    • Check that your Google Spreadsheet was added a new line with 1,2,3


    STEP 11 - ADD VALUES TO SHEET FROM ANY TASK


    • Add a Variable Set action to the start of the task and set %par1 to 1,2,3 if %par is not set
    (i) Setting %par1 to a value by default allows you to easily test this task in a standalone way instead of having to always test it from another task
    • Change the Array Set action in the task and set the Values field to %par1
    • Go back to the main screen and add a new task called Test Append
    • Use the Perform Task action and set the Name to Append
    • Set Parameter 1 to some values you wish to insert like a,b,c,d,e
    • Run the task and check that the values were correctly inserted in a new row on your spreadsheet!



    Done!! Now, whenever you need to add a row to your spreedsheet from Tasker, simply call the Append task and set Parameter 1 to the values you wish to insert as shown in the example! :cool:

    EXTRA
    If you want, you can also make voice notes from your Google Assistant and Google Home. Like this:


    All in one go (something like "note to self take out the trash"):
    • On IFTTT create an applet with the Google Assistant "this" part and set your commands to something like note to self $ and take a note $
    • In the "that" part use the Webhook action, generate a Join URL by using the JOIN API button here. You should use a URL like this: https://joinjoaomgcd.appspot.com/_ah/api/messaging/v1/sendPush?text=selfnote=:= <<<{{TextField}}>>>&deviceNames=YOUR_DEVICE_NAME&apikey=YOUR_API_KEY (replace YOUR_DEVICE_NAME and YOUR_API_KEY with your own values)
    With a pause (something like "note to self"..."take out the trash"):
    • In Google Assistant settings create a routine that when you say note to self or take a note it'll do Ask AutoVoice to take a note
    • In AutoVoice, import this natural language command. (you can simply click on this link on your phone and ask to open in AutoVoice, or you can open AutoVoice > Natural Langugage > Commands > + button > import > remote > insert URL above)
    • Make sure to enable the Use For Google Assistant/Alexa option in AutoVoice > Natural Language > Commands
    Both of these options will work if you use the project linked to in the download link. Enjoy! :)
    HUMENTH likes this.