Sending data from a Google Doc to a Sheet via scripts

The backdrop

I recently completed a research based assignment for a course I am doing. The paper was written completely as a Google doc and submitted via TurnItIn. As you know, such papers require tons of referencing to backup the claims we are making in them. Often times keeping track of these resources – books, websites, journals, videos – can be hard. In the past I have tried organizing them in a separate Google sheet for later perusal/citation but it is always cumbersome to keep switching back and forth between browser tabs to make this happen. I know there are ready made apps for this kind of stuff but hey, where is the fun in that? This challenge was forcing me to code in Google docs, which I rarely do. So I took it up.

I decided to try writing a script that accepts my reference entries from a Google document and pressing a button would automatically send that entry into a Google sheet. More entries would come in the same way and the data in the sheet would keep getting appended.

Here’s the catch though. I wanted it neatly organized too so that I can manage it better later for citations. So the Google sheet had 4 columns, each titled by the kind of resource it was. The script was supposed to identify what kind of resource is coming in and put it in the right column accordingly.  

The requirements

In Computer Science one of the core things we teach students is to collect end-user requirements. So applying it on myself, I made the following notes:

  • A sidebar should open in the Google doc that allows for accepting of reference entries.
  • The sidebar, coded in HTML, should contain a basic form that has a submit button whose job it is to push the value into the sheet.
  • The form should also have a classifier (like a dropdown) that identifies what kind of resource it was.
  • Based on the choice made in the form, that entry would then automatically go to the respective pre-set column on the Google sheet. 
  • Bonus: To avoid keeping the Google sheet open, see if a published version of it can be embedded into the sidebar HTML.  

The code

  1. The first step was to go to the Google document here the references will be created. Once there, I went to Script Editor under Tools.
Tools >> Script Editor

The code has two files: (the Google Script file that captures the data from this file to send to the Google Sheet) and sendForm.html which contains the form where user inputs the data.

This google script file has three functions. They are documented below.

onOpen() function that triggers everytime the Google doc opens

openSidebarForm() opens an HTML sidebar inside an iFrame

processForm( ) accepts data from the form, checks it for specific values and pushes it into the Google Sheet.


The second file is the HTML service that interacts with server-side Google script functions. 

sendForm.html that contains the HTML form and script to trigger posting of data to the Google script.

The output

Executed output sample

Concluding thoughts

  • So, the solution works. The citations still need to be created depending on the type (APA, MLA, Harvard etc) but this kind of collection works for me to organize myself. 
  • The HTML form can be modified to accommodate more useful information such as : context, purpose, author info. etc In a long spanning research project such a collection could prove quite useful. 
  • I tried embedding the Google sheet onto the HTML form on the sidebar but it looked very busy and results did not update as quickly as I’d have hoped. But a link to the sheet can exist on the HTML page.
  • A version 2,0 of such a solution could potentially include direct referencing too so that ready to use citations are created depending on the type of citation required. This obviously means more code that parses the text and looks at author, organization, content type. Something to develop as a bigger project perhaps.