< Warning >
I recommend you to get extra Google account only for public.
Because if you follow my procedure, your Gmail address will be on public JSONP file.
< Can I put adsense codes on HTML on Google Drive? >
The answer is NO. I found the important information on Google Product Forums below.
Can I place adsense codes (ads) in my google drive every doucement?
You can not place adsense codes on any documents in Google Drive. Please be careful.
< Google Spreadsheets >
Old version of Google Spreadsheets can share spreadsheets as XML, JSON and some kind of format. I thought new version doesn’t have such sharing functions. But I found the good solution for new version of Google Spreadsheets on the answer for the question on StackOverflow.
Unable to publish specific cells in new Google Spreadsheets – StackOverflow
According to the page above, this API is a kind of old API. No one is sure when it stops. I don’t care this situation. But if you don’t like the situation, I don’t recommend you to use this API.
< URL format >
https://spreadsheets.google.com/feeds/cells/[ Your spreadsheet key ]/od6/public/values?min-row=[ start row number ]&max-row=[ end row number ]&min-col=[ start column number ]&max-col=[ end column number ]&alt=json-in-script&callback=[ callback function name ]
< How to use it? >
JSONP format is easy to use in JavaScript. No one need parser. But I want to use URL generator to make the API more useful. So I developed a script that converts from paging parameters to URL parameters following Google syntax.
< jQuery plugin >
I developed this jQuery plugin named “generateGoogleJsonpUri”.
generateGoogleJsonpUri – github
I created sample page like a blog framework to test my plugin.
http://admin.mecrazy.net/sample/generateGoogleJsonpUri/
If you want, please copy its source as an example.
< Usage – casting >
Casting syntax is
var test = $.genJsonpGDU("<Your-spreadsheet-key-here>");
or
var test = $.genJsonpGDU({key:"<Your-spreadsheet-key-here>"});
.
You can find a key in the URL of your spreadsheet. It is my sample below.
On this picture, URL is “https://docs.google.com/spreadsheets/d/1IG2QEOXehU01b6DoMQi19CvFpm2EZJOUM2y4lqXKduw/edit#gid=0”. The key for my spreadsheet is “1IG2QEOXehU01b6DoMQi19CvFpm2EZJOUM2y4lqXKduw”. Please check the URL of your spreadsheet to get your key.
< Usage – configure pager >
test.pager({ startRow:2,//Row number to start getting (required) minCol:2,//Column number to start getting (required) maxCol:7,//Column number to end getting (required) recsPerPage:5//Records in a page (optional) });
Please compare the syntax above and the table below. When you see the table below, please imagine Excel or any kind of spreadsheet. Syntax above means the range of the first page is pink range, 2nd page is green range. “B2:G2” is a first record. “B6:G6” is a fifth record.
A1 | B1 | C1 | D1 | E1 | F1 | G1 | H1 |
A2 | B2 | C2 | D2 | E2 | F2 | G2 | H2 |
A3 | B3 | C3 | D3 | E3 | F3 | G3 | H3 |
A4 | B4 | C4 | D4 | E4 | F4 | G4 | H4 |
A5 | B5 | C5 | D5 | E5 | F5 | G5 | H5 |
A6 | B6 | C6 | D6 | E6 | F6 | G6 | H6 |
A7 | B7 | C7 | D7 | E7 | F7 | G7 | H7 |
A8 | B8 | C8 | D8 | E8 | F8 | G8 | H8 |
A9 | B9 | C9 | D9 | E9 | F9 | G9 | H9 |
A10 | B10 | C10 | D10 | E10 | F10 | G10 | H10 |
A11 | B11 | C11 | D11 | E11 | F11 | G11 | H11 |
A12 | B12 | C12 | D12 | E12 | F12 | G12 | H12 |
A13 | B13 | C13 | D13 | E13 | F13 | G13 | H13 |
< Usage – get page URL >
It is an example to get first page URL.
var pageUrl = test.page(1);
( “test.pager(object)” is required before executing this function. )
< Usage – execute Ajax >
Arguments for “test.ajax” are the same as “http://api.jquery.com/jQuery.ajax/“.
test.ajax({ url:test.page(1), success:function(json){ console.log(json); } });
If you want to know current page has previous page or next page, please use “test.get()” in success function and check parameters.
test.ajax({ url:test.page(1), success:function(json){ console.log(json); var param = test.get(); if(param.pager.prev){ console.log('Current page has previous page.'); }else{ console.log('Current page does not have previous page.'); } if(param.pager.next){ console.log('Current page has next page.'); }else{ console.log('Current page does not have next page.'); } } });
< Usage – convert JSON to 2D array >
“test.jsonToArr” converts from Google’s JSON format to 2D array.
test.ajax({ url:test.page(1), success:function(json){ var arr2d = test.jsonToArr(json); console.log(arr2d); } });
< Usage – variable cache >
If you want to enable paging cache using JavaScript variables, please follow the syntax below.
test.set({cache:true});
This argument switches to use cache for paging. It checks update date on JSONP every 20 minutes by default. If the date changed, it automatically clear cache. If you want to change the interval to check the date. You can use the syntax like below instead of the syntax above.
test.set({ cache:true, cacheInterval:{ sec:0,min:50,hour:0 } });
< Afterword >
There are basic differences between new and old Google Spreadsheets.
New | Old | |
Maximum cells | 2 million cells | 400,000 cells |
Maximum columns | Unlimited | 256 columns per sheet |
I think the new version is unbelievably big and free database. I want to find interesting usages of new Google Spreadsheets.