Monday, 19 January 2015

Pure Javascript+html page application by using google sheets as backend and database

To quickly create a working web application, is not a easy thing. You need implement the front-end--html+js and the back-end and the database and link them together. Of course you also need setup your development environment and prepare a deployment environment to show your users.

If your application is just a html page without any backend and database. The html page can run locally and store data into a file, that would be perfect!!!  However, this is impossible!!!!  Accessing local file will be blocked by browser!!!

My solution is using javascript (ajax, angularjs or jquery) to call google sheet RESTful api to create, query update, delete data.

First create a google sheet like above.

You can access it by accessing this url:

Note the url format is:

"feed" could be "cell" or "list". cell is used to accessing a cell. "list" is used to accessing a line of data.

"key" is the long string in your sheet's url, which i marked on above snapshot.

"worksheet" this is odd. od6 is for the first sheet. I haven't found what is for the second and third.... So, you should also keep using "od6" and only using the first sheet. If you know what is for the second worksheet, please let me know!!!

If you use a RESTful client the result is more user friendly:

A <entry/> is a row (line) of data.

The "edit" link is the url for this particular line of data.


You can send  "get" request to this url to get data of this line.


You can send "put" request to update this line.

Note: very important!

1, the url of this line of data will be changed if you update it (the line id, which is the end of the url). So always query the target line of data, by "get" request. You can use the old url to query, it will return you <entry> xml with the new url.

2, in the above snapshot, the RAW Body is a xml, please not use the query return xml directly, because it doesn't have the "xmlns", google sheet may returns your an error.

The solution is access the url by your browser and it will show the full xml and you can directly use this xml.

The url must be changed, but the xml can be reuse (only change the data you want to change).


send "Delete" request to the same url of a line of data, you can delete it.

please note that, when you send "delete" request make sure you don't send any extral data:

Note: the line id (the end of the url) can be a old id.


To create a new line of data, you can send a "post" request, however, the url is different with above urls,  it should be:

As you can see this is still the old url but without the line id at the end.

Your new data will be inserted below the last data line.

No comments:

Post a comment