Sometimes it’s a hassle to track auth data for the Google Spreadsheet API. Here’s a quick hack using Google Forms to post data to a Spreadsheet (similar to the previous post that uses Curl). You can use it as a function in your code, or as a simple command-line tool.
Note: updated in 2023 to support new Google Forms HTML.
#!/usr/bin/python
"""Posts to a Google Sheet using a Form"""
import re
import sys
import urllib
import urllib2
def get_field_ids(form_url):
"""Returns list of field IDs on the form."""
response = urllib2.urlopen(form_url)
html = response.read()
form_field_info = re.findall('(<input|<textarea)[^>]*id="([^"]*)"', html)
if form_field_info: # depends on UI version
fields = [x[1] for x in form_field_info if x[1].startswith("entry")]
else:
form_field_info = re.findall('\[\[([0-9]{3,30}),null,0\]\]', html)
fields = ["entry."+x for x in form_field_info]
fields = [x[1] for x in form_field_info if x[1].startswith("entry")]
return fields
def post_form_values(form_url, submit_values):
"""Posts list of values to the form."""
fields = get_field_ids(form_url) # these could be cached too.
submit_url = form_url.replace("/viewform", "/formResponse")
values = {}
for counter in range(0, min(len(submit_values), len(fields))):
values[fields[counter]] = submit_values[counter]
data = urllib.urlencode(values)
req = urllib2.Request(submit_url, data)
response = urllib2.urlopen(req)
the_page = response.read()
# ignore response
def main():
"""Command-line usage possible too!"""
if len(sys.argv)<3:
print "Expected: form_url value1 [value2 ...]"
return
form_url = sys.argv[1]
values = sys.argv[2:]
post_form_values(form_url, values)
if __name__ == "__main__":
main()
Comments / questions
There's currently no commenting functionality here. If you'd like to comment, please use Mastodon and mention me ( @hi@johnmu.com ) there. Thanks!
Related pages
- Using Curl to add rows to a Google Spreadsheet without using an API (2015)
- Midjourney / Discord to Lightroom automation - step by step - for 2023 (2023)
- A python curls up to touch the tip of its own tail with its nose, forming the shape of a circle. (2019)
- How to use Google webmaster tools stats with Excel (2007)