Skip to content

Instantly share code, notes, and snippets.

@kristjan
Last active June 10, 2022 09:10
  • Star 8 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save kristjan/fa3dce7f3128b225e47a to your computer and use it in GitHub Desktop.
Google Spreadsheet row insertion example
# $auth from auth.sh
# $spreadsheet_id from get_spreadsheets.sh
# $worksheet_id from get_worksheets.sh
curl \
--header "Authorization: GoogleLogin auth=$auth" \
--header 'Content-Type: application/atom+xml' \
-d @data.xml \
-X POST \
"https://spreadsheets.google.com/feeds/list/$spreadsheet_id/$worksheet_id/private/full"
# Example data in data.xml
curl https://www.google.com/accounts/ClientLogin \
--data-urlencode Email=$email --data-urlencode Passwd=$password \
-d accountType=GOOGLE \
-d source=Google-cURL-Example \
-d service=wise
# If you have 2-factor auth enabled, you need to create an application password to use here
# Response includes an "Auth=..." line with your token
<!--
Note headers are lowercase. I haven't checked if that matters, but it's how Google response to a GET.
This schema matches the spreadsheet at https://docs.google.com/spreadsheets/d/1QG5xPBsIy5BHOvMBzgr_iSHAcx24HPgcg_B3GMh3qt4/edit?usp=sharing
which looks like:
Fruit | Color | Size
====================== <- Header bar in UI; not sure if this matters or if Google uses the first row anyway
Apple | Red | Medium
-->
<entry xmlns="http://www.w3.org/2005/Atom"
xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended">
<gsx:fruit>Orange</gsx:fruit>
<gsx:color>Orange</gsx:color>
<gsx:size>Medium</gsx:size>
</entry>
# $auth from auth.sh
# $spreadsheet_id from get_spreadsheets.sh
# $worksheet_id from get_worksheets.sh
curl \
--header "Authorization: GoogleLogin auth=$auth" \
"https://spreadsheets.google.com/feeds/list/$spreadsheet_id/$worksheet_id/private/full"
# Example response data:
# <title type="text">Apple</title>
# <content type="text">color: Red, size: Medium</content>
# <gsx:fruit>Apple</gsx:fruit>
# <gsx:color>Red</gsx:color>
# <gsx:size>Medium</gsx:size>
# $auth from auth.sh on the line reading Auth=...
curl \
--header "Authorization: GoogleLogin auth=$auth" \
"https://spreadsheets.google.com/feeds/spreadsheets/private/full"
# Look in this response for your spreadsheet ID / worksheet fetch URL in the <link rel="...#worksheetsfeed" href="..." />
# Example spreadsheet: https://docs.google.com/spreadsheets/d/1QG5xPBsIy5BHOvMBzgr_iSHAcx24HPgcg_B3GMh3qt4/edit?usp=sharing
# GET response: <link rel="http://schemas.google.com/spreadsheets/2006#worksheetsfeed" type="application/atom+xml" href="https://spreadsheets.google.com/feeds/worksheets/1QG5xPBsIy5BHOvMBzgr_iSHAcx24HPgcg_B3GMh3qt4/private/full"/>
# URL / $spreadsheet_id from get_spreadsheets.sh
curl \
--header "Authorization: GoogleLogin auth=$auth" \
"https://spreadsheets.google.com/feeds/worksheets/$spreadsheet_id/private/full"
# Look for your worksheet ID / rows URL in <link rel="...#listfeed" href="..." />
# Eg, this shows worksheet ID "od6"
# <link rel="http://schemas.google.com/spreadsheets/2006#listfeed" type="application/atom+xml" href="https://spreadsheets.google.com/feeds/list/1QG5xPBsIy5BHOvMBzgr_iSHAcx24HPgcg_B3GMh3qt4/od6/private/full"/>
@bogdanr
Copy link

bogdanr commented Jun 14, 2016

auth.sh doesn't work anymore.
It returns the URL https://developers.google.com/accounts/docs/AuthForInstalledApps which essentially tells us that this has been deprecated since 2012.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment