Google Apps Script - Part 3 - Send email with form after submitting google form (advanced)
So, you now know how to send people email after submitting a google form? How about including in that email a form required that person to answer some questions? And the data that she submit will be inserted back into the response spreadsheet of the original form. Yeah, another form, sounds like inception huh :D
Here is a hack:
1. In the email template, include the form:
<h2>Please respond to the reflection question(s) posed to you in the Teacher Feedback form</h2>
<form action="http://localhost:8081/myform" method="POST">
<input type="hidden" name="rownumber" value="{{rownumber}}">
<div>
<textarea name="q" style="width:90%;height:100px;"></textarea>
</div>
<div>
<button type="submit">Submit</button>
</div>
</form>
The form will look something like this in the email:
Notes:
2. Create a Django project to receive the submitting data and insert that data into the responses spreadsheet using the gspread library.
views.py:
urls.py:
3. Run the Django app at port 8081 (or whatever port you want as long as it is the same in the template)
4. When the user receive the email after you submitting the original form >> she fill in the form in the email, click submit >> the data will be record back in to the responses spreadsheet.
Neat!!!
Update (January 22, 2016): ClientLogin has been officially deprecated since April 20, 2012 and is now no longer available. Requests to ClientLogin will fail with a HTTP 404 response (more). So, we have to use the OAuth2 authentication method instead (follow this article to setup a service account with your google account):
1. Create a service account that has access to Drive API and download the json file and put it somewhere.
2. Share the responses spreadsheet with the client_email email address from the json file.
3. Install the dependencies:
(myvenv)trinh@myserver: pip install --upgrade oauth2client
(myvenv)trinh@myserver: pip install --upgrade PyOpenSSL
(myvenv)trinh@myserver: pip install --upgrade pycrypto
4. Access the responses spreadsheet like the following:
import json
import gspread
from oauth2client.client import SignedJwtAssertionCredentials
json_key = json.load(open('/path/to/service_account.json'))
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'].encode(), scope)
gc = gspread.authorize(credentials)
wks = gc.open("<spreadsheet id>").sheet1
Here is a hack:
1. In the email template, include the form:
<h2>Please respond to the reflection question(s) posed to you in the Teacher Feedback form</h2>
<form action="http://localhost:8081/myform" method="POST">
<input type="hidden" name="rownumber" value="{{rownumber}}">
<div>
<textarea name="q" style="width:90%;height:100px;"></textarea>
</div>
<div>
<button type="submit">Submit</button>
</div>
</form>
The form will look something like this in the email:
Notes:
- action="http://localhost:8081/myform" => a django app running at port 8081 on my machine to process submitting data
- {{rownumber}} => will be filled in by the lastrow number of the response spreadsheet in the google apps script (return_message = return_message.replace("{{rownumber}}", lastrow);)
2. Create a Django project to receive the submitting data and insert that data into the responses spreadsheet using the gspread library.
views.py:
urls.py:
from django.conf.urls import patterns, include, url
urlpatterns = patterns('',
url(r'^myform$', 'apps.myapp.views.myform', name='myform'),
)
3. Run the Django app at port 8081 (or whatever port you want as long as it is the same in the template)
4. When the user receive the email after you submitting the original form >> she fill in the form in the email, click submit >> the data will be record back in to the responses spreadsheet.
Neat!!!
Update (January 22, 2016): ClientLogin has been officially deprecated since April 20, 2012 and is now no longer available. Requests to ClientLogin will fail with a HTTP 404 response (more). So, we have to use the OAuth2 authentication method instead (follow this article to setup a service account with your google account):
1. Create a service account that has access to Drive API and download the json file and put it somewhere.
2. Share the responses spreadsheet with the client_email email address from the json file.
3. Install the dependencies:
(myvenv)trinh@myserver: pip install --upgrade oauth2client
(myvenv)trinh@myserver: pip install --upgrade PyOpenSSL
(myvenv)trinh@myserver: pip install --upgrade pycrypto
4. Access the responses spreadsheet like the following:
import json
import gspread
from oauth2client.client import SignedJwtAssertionCredentials
json_key = json.load(open('/path/to/service_account.json'))
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'].encode(), scope)
gc = gspread.authorize(credentials)
wks = gc.open("<spreadsheet id>").sheet1