Google Sheets
is a very popular spreadsheet application that anyone with a Google account can use for free. It is very intuitive and even people with no prior experience of using sheets will find it quite easy to use.
You are just starting to learn web programming and want to build your own website but do not have the back-end skills like Node.Js
, Ruby
or Python
, this is the article for you. Or if you want to create a simple blog-style website or porfolio, or if you want to quickly build a prototype / mockup for a new feature, this is also the article for you.
In this article, I will introduce to use Google Sheets
as Database for your website.
Things you need to know: HTML, CSS, JavaScript, jQuery, Ajax basics.
How to do the following:
The method is quite simple, you just need to fetch data from sheets, there are quite a lot of options for this, you can use the Google Sheets API .
Step 1: Create your sheets page example . Then publish it first to get the JSON response. Click File
– Publish
Then copy the Id starting after / d / to the end.
Step 2: Now take a look at this URL
1 2 | https://spreadsheets.google.com/feeds/cells/SHEET_CODE/SHEET_NUMBER/public/full?alt=json |
Open Google Sheet, then copy the sheet code
sheet number
and the sheet number
, for example:
1 2 | https://docs.google.com/spreadsheets/d/1M4a_1NT7sws0rUA5aZnE4ERZmO39ustwr0NQKL6vlzE |
Then the sheet code
is 1M4a_1NT7sws0rUA5aZnE4ERZmO39ustwr0NQKL6vlzE and the sheet number
is 1
Instead of above, now try this URL
1 2 | https://spreadsheets.google.com/feeds/cells/1M4a_1NT7sws0rUA5aZnE4ERZmO39ustwr0NQKL6vlzE/1/public/full?alt=json |
You can get JSON
from Sheets without using a third-party tool.
Step 2.1: You can use the tool to get a nice JSON response
(which will filter out unnecessary things) like Tabletop.js
Integrate Google Sheets into your website
First, use a static website first. Then add this, we will use TableTop
:
1 2 | <script src='https://cdnjs.cloudflare.com/ajax/libs/tabletop.js/1.5.1/tabletop.min.js'></script> |
Then write the init function as follows:
1 2 3 4 | <span class="token keyword">function</span> <span class="token function">init</span> <span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">{</span> Tabletop <span class="token punctuation">.</span> <span class="token function">init</span> <span class="token punctuation">(</span> <span class="token punctuation">{</span> key <span class="token punctuation">:</span> sheetUrl <span class="token punctuation">,</span> callback <span class="token punctuation">:</span> showInfo <span class="token punctuation">,</span> simpleSheet <span class="token punctuation">:</span> <span class="token boolean">true</span> <span class="token punctuation">}</span> <span class="token punctuation">)</span> <span class="token punctuation">}</span> |
Write the showInfo function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | <span class="token keyword">function</span> <span class="token function">showInfo</span> <span class="token punctuation">(</span> data <span class="token punctuation">,</span> tabletop <span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">for</span> <span class="token punctuation">(</span> <span class="token keyword">var</span> i <span class="token operator">=</span> <span class="token number">0</span> <span class="token punctuation">;</span> i <span class="token operator"><</span> data <span class="token punctuation">.</span> length <span class="token punctuation">;</span> i <span class="token operator">++</span> <span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token comment">// Đây là cấu trúc tĩnh của bạn, bạn chỉ cần đưa data từ lấy từ sheet vào</span> <span class="token comment">// Ví dụ muốn lấy cột name dòng i thì là data[i].name</span> <span class="token function">$</span> <span class="token punctuation">(</span> <span class="token string">'.postlist'</span> <span class="token punctuation">)</span> <span class="token punctuation">.</span> <span class="token function">append</span> <span class="token punctuation">(</span> ' <span class="token operator"><</span> div <span class="token keyword">class</span> <span class="token operator">=</span> <span class="token string">"col-md-4"</span> <span class="token operator">></span> <span class="token operator"><</span> h4 style <span class="token operator">=</span> <span class="token string">"text-align:center;"</span> <span class="token operator">></span> <span class="token string">'+data[i].name+'</span> <span class="token operator"><</span> <span class="token operator">/</span> h4 <span class="token operator">></span> <span class="token operator"><</span> img <span class="token keyword">class</span> <span class="token operator">=</span> <span class="token string">"img-thumbnail rounded mx-auto d-block"</span> style <span class="token operator">=</span> <span class="token string">"height:200px;"</span> alt <span class="token operator">=</span> <span class="token string">"'+data[i].name+'"</span> src <span class="token operator">=</span> <span class="token string">"'+data[i].imagesrc+'"</span> <span class="token operator"><</span> <span class="token operator">/</span> img <span class="token operator">></span> <span class="token operator"><</span> p style <span class="token operator">=</span> <span class="token string">"text-align: justify; text-justify: inter-word;"</span> <span class="token operator">></span> <span class="token string">' + data[i].summary + '</span> <span class="token operator"><</span> <span class="token operator">/</span> p <span class="token operator">></span> <span class="token operator"><</span> p <span class="token operator">></span> <span class="token operator"><</span> b <span class="token operator">></span> Status <span class="token punctuation">:</span> <span class="token operator"><</span> <span class="token operator">/</span> b <span class="token operator">></span> <span class="token string">'+ data[i].status + '</span> <span class="token operator"><</span> <span class="token operator">/</span> p <span class="token operator">></span> <span class="token operator"><</span> p <span class="token operator">></span> <span class="token operator"><</span> b <span class="token operator">></span> Built Date <span class="token punctuation">:</span> <span class="token operator"><</span> <span class="token operator">/</span> b <span class="token operator">></span> <span class="token string">'+ data[i].builtdate + '</span> <span class="token operator"><</span> <span class="token operator">/</span> p <span class="token operator">></span> <span class="token operator"><</span> p <span class="token operator">></span> <span class="token operator"><</span> b <span class="token operator">></span> Destroyed Date <span class="token punctuation">:</span> <span class="token operator"><</span> <span class="token operator">/</span> b <span class="token operator">></span> <span class="token string">'+ data[i].destroyeddate + '</span> <span class="token operator"><</span> <span class="token operator">/</span> p <span class="token operator">></span> <span class="token operator"><</span> p <span class="token operator">></span> <span class="token operator"><</span> b <span class="token operator">></span> Destroyed by <span class="token punctuation">:</span> <span class="token operator"><</span> <span class="token operator">/</span> b <span class="token operator">></span> <span class="token string">'+ data[i].destroyedby + '</span> <span class="token operator"><</span> <span class="token operator">/</span> p <span class="token operator">></span> <span class="token operator"><</span> p <span class="token operator">></span> <span class="token operator"><</span> a <span class="token keyword">class</span> <span class="token operator">=</span> <span class="token string">"btn btn-outline-info"</span> target <span class="token operator">=</span> <span class="token string">"_blank"</span> href <span class="token operator">=</span> <span class="token string">"'+data[i].details_link+'"</span> <span class="token operator">></span> View details <span class="token operator"><</span> <span class="token operator">/</span> a <span class="token operator">></span> <span class="token operator"><</span> <span class="token operator">/</span> p <span class="token operator">></span> <span class="token operator"><</span> <span class="token operator">/</span> div <span class="token operator">></span> ' <span class="token punctuation">)</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> window <span class="token punctuation">.</span> <span class="token function">addEventListener</span> <span class="token punctuation">(</span> <span class="token string">'DOMContentLoaded'</span> <span class="token punctuation">,</span> init <span class="token punctuation">)</span> |
That's it and now you just need to run the website to see the results.
Also you can use some other tools to interact with your sheets, I found a pretty good tool is BlockSpring You can query data, add new data to sheets with code.
Select data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | <span class="token comment">// First line of function</span> blockspring <span class="token punctuation">.</span> <span class="token function">runParsed</span> <span class="token punctuation">(</span> <span class="token string">"query-google-spreadsheet"</span> <span class="token punctuation">,</span> <span class="token punctuation">{</span> <span class="token comment">// Here is where you need to decide what data you are grabbing with the Google query syntax</span> <span class="token comment">// I am finding elements that match the variable num, which is from a previous part of the code</span> <span class="token comment">// Google syntax makes you request columns with letters as opposed to your column titles.</span> <span class="token string">"query"</span> <span class="token punctuation">:</span> <span class="token string">"SELECT A, C WHERE B ="</span> <span class="token operator">+</span> num <span class="token punctuation">,</span> <span class="token comment">// This is the full URL of your sheet. Just copy and paste from your browser.</span> <span class="token string">"url"</span> <span class="token punctuation">:</span> <span class="token string">"YOURURLHERE"</span> <span class="token comment">//Cache is false so that your browser does not run an old function when you cnange your code</span> <span class="token punctuation">}</span> <span class="token punctuation">,</span> <span class="token punctuation">{</span> cache <span class="token punctuation">:</span> <span class="token boolean">false</span> <span class="token punctuation">,</span> expiry <span class="token punctuation">:</span> <span class="token number">7200</span> <span class="token punctuation">}</span> <span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span> res <span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token comment">// All results are part of the res.params.data object.</span> <span class="token comment">// in my case, I requested two columns from my spreadsheet, one named skill and one named key.</span> <span class="token comment">//There is one matching row in this case, with two elements</span> <span class="token keyword">var</span> skill <span class="token operator">=</span> res <span class="token punctuation">.</span> params <span class="token punctuation">.</span> data <span class="token punctuation">[</span> <span class="token number">0</span> <span class="token punctuation">]</span> <span class="token punctuation">[</span> <span class="token string">'skill'</span> <span class="token punctuation">]</span> <span class="token punctuation">;</span> <span class="token keyword">var</span> key <span class="token operator">=</span> res <span class="token punctuation">.</span> params <span class="token punctuation">.</span> data <span class="token punctuation">[</span> <span class="token number">0</span> <span class="token punctuation">]</span> <span class="token punctuation">[</span> <span class="token string">'key'</span> <span class="token punctuation">]</span> <span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> |
Add new
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | <span class="token comment">//Blockspring APIs take in data as if it was from a spreadsheet. Needs nested arrays</span> <span class="token comment">//Like this: [[row1val1, row1val2],[row2val1, row2val2]...]</span> <span class="token comment">//Example from Blockspring site</span> <span class="token keyword">var</span> orig <span class="token operator">=</span> <span class="token string">"[["name","random number"],["Jason","150"],["Don","250"],["Paul","50"]]"</span> <span class="token comment">//Add values to sheet</span> blockspring <span class="token punctuation">.</span> <span class="token function">runParsed</span> <span class="token punctuation">(</span> <span class="token string">"append-to-google-spreadsheet"</span> <span class="token punctuation">,</span> <span class="token punctuation">{</span> <span class="token comment">//middle parameter from Google Spreadhseet URL </span> <span class="token comment">//https://docs.google.com/spreadsheets/d/FILE_ID/edit?usp=sharing</span> <span class="token string">"file_id"</span> <span class="token punctuation">:</span> <span class="token string">'YOURIDHERE'</span> <span class="token punctuation">,</span> <span class="token comment">// The first sheet within the particular doc will always be 0</span> <span class="token string">"worksheet_id"</span> <span class="token punctuation">:</span> <span class="token number">0</span> <span class="token punctuation">,</span> <span class="token comment">//The array of arrays, as stated above </span> <span class="token string">"values"</span> <span class="token punctuation">:</span> allData <span class="token punctuation">}</span> <span class="token punctuation">,</span> <span class="token comment">//Provided on the page at https://open.blockspring.com/pkpp1233/append-to-google-spreadsheet</span> <span class="token comment">//https://www.blockspring.com/docs/api_keys</span> <span class="token punctuation">{</span> <span class="token string">"api_key"</span> <span class="token punctuation">:</span> <span class="token string">"YOURAPIKEYHERE"</span> <span class="token punctuation">}</span> <span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span> res <span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token comment">//console.log to check for errors</span> console <span class="token punctuation">.</span> <span class="token function">log</span> <span class="token punctuation">(</span> res <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> |
Ok, I have finished talking about how to use Google Sheets
as a backend, now whenever you need to build a fast, simple website, I hope this article will help you.
Reference: https://dev.to/sarthakganguly/google-sheets-as-your-web-backend-4a65