cURLing Data to Google Sheets?

Jump to latest Follow Reply
stevenkan

stevenkan

Ars Legatus Legionis
15,662
  • #1
    Another part of my "science hive" project, is the charting of accumulated weight over time.

    1714694188884.png

    I'm logging the weight from 4 Stamps.com postage scales on a Raspberry Pi , to a text file, and then manually copying and pasting it into Excel when I want to update the chart 🤦🏻‍♂️.

    This would be a perfect use for the Google Sheets API, but I can't figure it out. I followed this guide all the way through, and I can get through every step except for actually updating the sheet:
    1. I have a project, the API, OAuth creds, and a token
      1. The instructions had (I think) a minor error in that the URL to obtain a token:
        1. Code: 
          https://accounts.google.com/o/oauth2/auth?client_id=[YOUR CLIENT-ID]&redirect_uri=http://localhost:3000&scope=https://www.googleapis.com/auth/drive.file
        2. returns "Required parameter is missing: response_type" so I took a guess and added "&response_type=code", and that returned:
        3. Code: 
          http://localhost:3000/?code=<really long code>&scope=https://www.googleapis.com/auth/drive.file
      2. The instructions also seem a bit incomplete, in that I think she intends that we repeat that process for each of the Scopes in that list of URLs, e.g.
        1. https://www.googleapis.com/auth/spreadsheets
        2. https://www.googleapis.com/auth/spreadsheets.readonly
        3. https://www.googleapis.com/auth/drive.readonly
        4. https://www.googleapis.com/auth/drive
      3. so I did that repeatedly and recorded the returned codes, then used those codes to request access tokens.
    2. His final step is to construct a cURL command to inject data into a sheet.
      1. I copied his example into InsertDataIntoGoogleSheet.sh, inserted my values, and ran it.
      2. My script returns
        1. HTML: 
          <a href=//www.google.com/><span id=logo aria-label=Google></span></a>
  <p><b>404.</b> <ins>That’s an error.</ins>
  <p>The requested URL <code>/v4/spreadsheets/1mUJuULK20EPo6AOjhvBusi0FHVH6fWPX3_dmuvKwqHc/values/Sheet1!A1:C1</code> was not found on this server.  <ins>That’s all we know.</ins>
        2. I don't care about privacy/security on that sheet, since it's just a dummy sheet for testing this data injection, and the sheet ID matches the sheet ID in the error.
      3. As a check, I inserted echo $ACCESS_TOKEN in his Line 20, just before construction of the cURL command to POST the data, and it does print out a long string that looks like a token, so I think that means I did everything above it correctly.
      4. Do his instructions look correct? Or is there a possibly syntax error in there?
    His original example:

    Code: 
    #!/bin/bash

# Define Some variable values to insert
value1="Server01"
value2="/var/www"
value3="85% Used"

# Set the spreadsheet ID and range
SPREADSHEET_ID="1w5TSDYt6Cbg8-_liMWDgTxgcSAb8JdpbPVTnzfj7E1I"
RANGE="Sheet1!A1:C1"

# Set the client ID and client secret
CLIENT_ID="251327740099-6667am9lnhjeffc22cequ1gdo1uchcbb.apps.googleusercontent.com"
CLIENT_SECRET="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"
REFRESH_TOKEN="1//0gg3yvsZe2NNVCgYIARAAGBASNwF-L9IrLiIg4_bSFdSBjz9OBrewqMMDHJWd1NNjUc_Kkw_O7MfJUXPHAJySNmK44KHD0KYy9o0"

# Construct cURL command to get Access Token
AUTH_RESPONSE=$(curl --request POST --data "access_type=offline&refresh_token=$REFRESH_TOKEN&client_id=$CLIENT_ID&client_secret=$CLIENT_SECRET&grant_type=refresh_token" https://oauth2.googleapis.com/token)
ACCESS_TOKEN=$(echo $AUTH_RESPONSE | jq -r '.access_token')

# Send the request to Google Sheets API
curl -s -X POST \
  -H "Authorization: Bearer $ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d "{\"values\": [[\"$value1\", \"$value2\", \"$value3\"]]}" \
  "https://sheets.googleapis.com/v4/spreadsheets/$SPREADSHEET_ID/values/$RANGE:append?insertDataOption=INSERT_ROWS&valueInputOption=USER_ENTERED"


    Thanks!
     
    K

    kaadray

    Ars Centurion
    259
  • #2
    That URL in the instructions is not a list to repeat, it is one URL that is wrapping in the blog content. That is why you had to add response_code - it is there if you do the full URL. So from the looks of it you are only getting a token with one scope, not all.

    You can also see this in her example response where she extracts the token/code - it has all the scopes listed in a single response.

    Also, if you look at the response, there is a %20 (space) between scopes, which may be why the URL that you thought was a list wraps in the content and isn't one long line (like the last part that overruns the ads). I assume you need that in yours to separate scopes (if you cut and paste her example into most browser address bars you'll see the space replaced with %20 very briefly before getting the response).
     
    • Like
    Reactions: continuum and stevenkan
    stevenkan

    stevenkan

    Ars Legatus Legionis
    15,662
  • #4
    So this is basically working:

    1717534287260.png

    There are some annoyances, but those are function of Google Sheets itself, and not of the API:
    1. The good news is that $RANGE:append?insertDataOption=INSERT_ROWS automagically finds the end of my existing data and inserts new rows, which means I don't need a dynamically-defined named range in order to pick up the new data in the chart, as I do when I copy/paste into Excel.
    2. But now I need to insert my formulae for summation, etc., which must be R1C1, and R1C1 references must be entered as used via the INDIRECT function, plus I need to escape the quotes, resulting in ugliness such as:
      1. DATA7='=IF(ISBLANK(indirect(\"RC[-6]\",false)),\"\",DATEVALUE(LEFT(indirect(\"RC[-6]\",false),10))+TIMEVALUE(MID(indirect(\"RC[-6]\",false),12,8)))'
      2. But it works.
    3. I'd like to keep the chart at the top, freeze the top, and have the latest data visible below the freeze, but, in a baffling feature omission, that just isn't possible.
    4. I'd like to have lines, but no dots, for my XY scatterplot, but I'm not seeing a way to do that, either.
    5. I still need to get the weather data in there, but as a proof of concept, this is working!
    Thanks!
     
    stevenkan

    stevenkan

    Ars Legatus Legionis
    15,662
  • #5
    Is there a cleaner way to inject an array of data, instead of this clumsy ordered list?

    Bash: 
    #!/bin/bash

DATA1=$1
DATA2=$2
DATA3=$3
DATA4=$4
DATA5=$5
DATA6='=IF(indirect(\"RC[-4]\",false),SUM(indirect(\"RC[-4]:RC[-1]\",false)),\"\")'
DATA7='=IF(ISBLANK(indirect(\"RC[-6]\",false)),\"\",DATEVALUE(LEFT(indirect(\"RC[-6]\",false),10))+TIMEVALUE(MID(indirect(\"RC[-6]\",false),12,8)))'
SPREADSHEET_ID=$6
RANGE=$7

# Set the client ID and client secret
CLIENT_ID="[insert client ID]"
CLIENT_SECRET="[insert client secret]"
REFRESH_TOKEN="[insert refresh token]"

# Construct cURL command to get Access Token
AUTH_RESPONSE=$(curl --request POST --data "access_type=offline&refresh_token=$REFRESH_TOKEN&client_id=$CLIENT_ID&client_secret=$CLIENT_SECRET&grant_type=refresh_token" https://oauth2.googleapis.com/token)
ACCESS_TOKEN=$(echo $AUTH_RESPONSE | jq -r '.access_token')

# Send the request to Google Sheets API
curl -s -X POST \
  -H "Authorization: Bearer $ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d "{\"values\": [[\"$DATA1\", \"$DATA2\", \"$DATA3\", \"$DATA4\", \"$DATA5\", \"$DATA6\", \"$DATA7\"]]}" \
  "https://sheets.googleapis.com/v4/spreadsheets/$SPREADSHEET_ID/values/$RANGE:append?insertDataOption=INSERT_ROWS&valueInputOption=USER_ENTERED"
     
    cburn11

    cburn11

    Ars Praetorian
    441
    Subscriptor
  • #6
    If by "cleaner" you mean to have your script dynamically create an array based on the parameters you pass to your script on the command line, you could try:

    Bash: 
    #!/bin/bash

json_file=/tmp/data.json

#  count total parameters passed
cParams=${#}

#  create an indexed array "params" of parameters 1 through param count - 2
params=( "${@:1:$((${cParams}-2))}" )

#  second to last passed parameter
SPREADSHEET_ID=${@:$((${#params[@]}+1)):1}

# last passed parameter
RANGE=${@:$((${#params[@]}+2)):1}


#  insert your SUM and timestamp cells at the end of the array
params[${#params[@]}]='=IF(indirect(\"RC[-4]\",false),SUM(indirect(\"RC[-4]:RC[-1]\",false)),\"\")'
params[${#params[@]}]='=IF(ISBLANK(indirect(\"RC[-6]\",false)),\"\",DATEVALUE(LEFT(indirect(\"RC[-6]\",false),10))+TIMEVALUE(MID(indirect(\>

value_str=

for index in ${!params[@]}; do
        param=${params[${index}]}
        value_str+="\"${param}\""
        [ $((${index}+1)) -lt "${#params[@]}" ] && value_str+=","
done

cat > ${json_file} << EOF
{
        "values":
            [   
                [
                        ${value_str}
                ]
            ]
}

EOF

    Running:
    Code: 
    user@host:/tmp$ ./script value1 value2 "value3 has spaces" value4 value5 SPREADSHEAT_ID_val RANGE_val

    would produce /tmp/data.json:


    Code: 
    user@host:/tmp$ jq '.' /tmp/data.json
{
  "values": [
    [
      "value1",
      "value2",
      "value3 has spaces",
      "value4",
      "value5",
      "=IF(indirect(\"RC[-4]\",false),SUM(indirect(\"RC[-4]:RC[-1]\",false)),\"\")",
      "=IF(ISBLANK(indirect(\"RC[-6]\",false)),\"\",DATEVALUE(LEFT(indirect(\"RC[-6]\",false),10))+TIMEVALUE(MID(indirect(\"RC[-6]\",false),12,8)))"
    ]
  ]
}

    You could modify your curl command to POST the json file: "curl -d @ /tmp/data.json ..."
     
    • Like
    Reactions: stevenkan
    You must log in or register to reply here.
    Jump to latest Follow Reply
    Jump to latest Follow Reply