Category Archives: Google

Google

Get new version of Google Spreadsheets as JSONP

< 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.
https://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.
spreadsheet_sample_001
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.

Generating dynamic web pages only with Google Drive

< 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 XML 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.

< New solution on 2014/07/07 >
I wrote the post Get new version of Google Spreadsheets as JSONP. If you want to use new version of Google Spreadsheets to generate dynamic web pages instead of the old version, please read that post.

< Updated on 2014/04/26 >
Please use Anyfile Notepad or any other text editor apps for Google Drive on Google Web Store instead of Drive Notepad. Because Drive Notepad was removed from store.

< Updated on 2014/05/12 >
Hey! Drive Notepad is back to Google Web Store!

< Recommendation >
I recommend you to install “Drive Notepad”. It’s an extension for Google Drive. When you install Drive Notepad, you need Chrome. But after connecting Drive Notepad to Google Drive, Chrome is not required. You can use Drive Notepad on any browsers.
https://chrome.google.com/webstore/detail/drive-notepad/gpgjomejfimnbmobcocilppikhncegaj?hl=en-GB
drive_notepad_en_us

< For updating contents from Spreadsheet >
You need to use “Google Spreadsheet version 1”. Because “version 2” doesn’t support CSV and XML sharing yet. ( I wrote this post in April 19 2014. ) If you normally generate new spreadsheet today, it’s automatically be version 2.

Google explained new spreadsheet on “Check out the new Google Sheets“.

I worried about CSV and XML sharing will not support in version 2. So I checked also “Unsupported features in the new Google Sheets“. But this function isn’t listed on that page. Yep … not listed … but nobody is sure when Google change their mind….
Can you agree this situation? LOL

Let’s access https://g.co/oldsheets to generate a spreadsheet in version 1.

It’s my sample.

It’s only one sheet. It has header, 3 cols and 2 rows.
( I’m going to generate imgur image web site. )

< Let’s make it public >
Follow the pic below.
publish_to_the_web_en_us

Select “Publish to the web” and push “Start publishing”.
See “Get a link to the published data” section.
publish_to_the_web_2_en_us
You can get your spreadsheet as Web page, HTML, CSV, TXT, PDF, ATOM, RSS, XLS and ODS.
If you want to sync spreadsheet to your website, you should choose CSV, ATOM or RSS. These are easy format for syncing. When you choose one of them, you can find the function to select column and row. This function can make file size per request smaller.

If you choose CSV, before selecting range, please select a target sheet. Sample is like the pic below.
select_sheet_en_us
In my sample, “A1” is header. I want to get 5 rows maximum. So I set the range “A2:A6”.
select_cell_en_us
You can find “range=A2%3AA6” in the url. If you want to add paging function to your web site, you should dynamically change the parameter for “range” by Javascript.

< Is it difficult? >
Don’t worry. I wrote the Javascript code here.
https://github.com/mecrazy/generateGoogleDriveUri
This script is compatible with CSV, ATOM and RSS formats. It’s only URI generator. It doesn’t have parser. If you choose ATOM or RSS, you can use jQuery as a parser.

Please use “generateGoogleDriveUri.min.js” like below.

<!DOCTYPE html>
<html>
<head>
<title>Sample page</title>
<script src="js/generateGoogleDriveUri.min.js"></script>
<script type="text/javascript">
window.onload = function(){

//Casting object
var gduObj = new genGDU('Put a link to the published data here');

//Generate URI
var atomUri = gduObj.genUri(
  'atom', // I choose ATOM format.
  {
    startRow:2, //Start from row 2
    startCol:1, //Start from col 1
    cols:1, //Cut 1 columns
    rows:5, //Cut 5 Rows per page
    page:1 //Get page 1
  }
);

var html = '<a href="' + atomUri + '" target="_blank">' + atomUri + '</a>';
document.getElementById('sample').innerHTML = html;

}
</script>
</head>
<body id="sample"></body>
</html>

< Add index.html and Drive Notepad setting >
After you add Drive Notepad on Chrome, you can find “Text Document” on create menu of your Google Drive.
add_index_en_us
If it’s your first time to use Drive Notepad, you will be asked like below.
permission_01_en_us
Push “Login and/or grant app permissions…” and accept the question below.
permission_02_en_us

Please change file name to “index.html”. After saving the file, close the tab.
change_file_name_en_us

Please change setting about “Manage apps”.
manage_apps_en_us
Please check “Use by default” of Drive Notepad row.
use_by_default_en_us
After that, you can use Drive Notepad like the other apps on Google Drive.

< Change sharing settings >
Select your folder to set public.
After that, follow the pic below.
set_sharing_enabled_01_en_us
Set “Public on the web” checked.
set_sharing_enabled_02_en_us

< Preparing HTML, CSS, Javascript >
Use Drive Notepad to prepare them.
Or edit with your own text editor and upload them.
My relative position is like below.
my_public_tree_en_us

The link below is my web page created with Google Spreadsheet.
https://googledrive.com/host/0B2hsIY7iHEnLS290OHlmRkFmbU0/
If you want, please use sources as sample.

< Extra script >
I developed imgur URL converter for my web page. If you want, please use this.
https://github.com/mecrazy/imgurUrlConverter