Author Archives: mecrazy

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.

High-quality sound earphones are not expensive.

This post is not about computers. Today I write about earphones.

< I stopped to use expensive earphones. >
A wire of earphone is break in the future. We can’t escape from it. But I have bought expensive earphones to listen to music in high-quality sound. It gave me good sound, but when it break I’m so sad….so sad. I really wanted cheap high-quality sound earphones. I checked many web sites and got good solution for it. My solution is using Panasonic earphones with SONY ear-piece.

< SONY ear-piece >
SONY ear-piece EP-EX11 series are able to connect to some of Panasonic earphones. You can buy EP-EX11 on Rakuten Global Market or you can search any shops on the internet. Size variations are SS, S, M, L and LL. Color variations are black and white.

< Panasonic earphones >
Some models of Panasonic earphones are fit with SONY EP-EX11. I tried RP-HJE120, RP-HJE150, RP-HJS150 and RP-TCM19. These are all possible to connect with SONY’s ear-piece.

< RP-HJE120 >
Its wire is 110 cm.
Amazon.com – RP-HJE120

< RP-HJE150 >
Its wire is 120 cm.
Amazon.com – RP-HJE150

< RP-HJS150 >
It’s short wire version. Its wire is 60 cm.
Amazon.com – RP-HJS150

< RP-TCM19 >
This earphone is with remote control and microphone for smartphone. Please be careful not to break, because this model’s wire is thin.
Amazon.com – RP-TCM19

< Put EP-EX11 on Panasonic earphones >
It’s RP-HJE150 without ear-pieces.
step_001

I turned over EP-EX11 to make easier to put them on earphones.
step_002

I insert earphones forcibly to ear-pieces.
step_003

Complete.
step_004

Basic HTML generator for Bootstrap 3

< What’s this? >
It generates basic HTML source for Bootstrap 3. You can choose options when you generate it. I often use this for myself. If you want, please bookmark this page.


< Generator >
Page Title :
Language code :
Bootstrap version :
Compatible with old IE :
Use fixed navbar :
Use sticky footer :
Use jQuery failover :
Use jQuery UI failover :
Use backbone.js :
Use underscore.js :
Use bootbox :

If you choose “Compatible with old IE ” “YES”, you need to put “bootstrap.min.css” and “respond.min.js” on your server. Basically “respond.min.js” doesn’t work correctly on CDN and servers of others.

< Scripts >
You can download scripts that HTML uses. You can put on your server and replace paths of scripts.
Bootstrap
jQuery
jQuery UI
backbone.js
underscore.js
bootbox
html5shiv
Respond.js
( Bootstrap and Respond.js are necessary to download for old IE. Please be careful. )

Make STINGER3 (WordPress theme) compatible with WPML (Multilanguage plugin)

< What’s WPML? >
It is a multilingual plugin for WordPress. I use WPML on this mecrazy blog.
Official website of WPML

< What’s STINGER3? >
This is a theme for WordPress. It has many nice functions without plugin. For example, snippet inserter for ads(like Google Adsense) and cool design social buttons. I have wanted to try to use this theme.
Official website of STINGER3 (Japanese page)

< Hope gentle activity >
The developer of STINGER3 is too busy to answer on Twitter, and I’m not sure he can read languages other than Japanese. If you have any questions about STINGER3, I hope you to find the answer on Google or solve the problems by yourself.

< For the people who can’t read Japanese >
Official website of STINGER3 is in Japanese. I think it’s difficult to find download link of theme zip file. You should find the Japanese word “ダウンロード“. It means “download” in English. Please use CTRL + F to find this word. Please find the current version file name “stinger3ver20140327.zip”.

< Code for compatibility >
The code below give WPML compatibility to STINGER3.

<style type="text/css">

.submenu-languages{
  background-color:#f3f3f3;
  border-style:solid;
  border-color:#dedede;
  border-width:0px 1px 1px 1px;
  position:absolute;
  margin-left:-11px;
  margin-top:21px;
  padding-bottom:5px;
  display:none;
}
.submenu-languages > li{
  white-space:nowrap !important;
  float:none !important;
  display:inline-block !important;
}

.iclflag{ vertical-align:middle;position:relative !important;top:-1px !important; }
#lang_sel{ z-index:1000; }
#lang_sel a{ display:block; }
#lang_sel ul,#lang_sel li,#lang_sel a{
  margin:0px;padding:0px;
  width:300px;
  text-align:left;
  white-space:nowrap;
}
#lang_sel ul,#lang_sel li{ margin:0px !important;padding:0px !important; }
#lang_sel a{ margin:0px !important;padding:0px 7px !important; }
#lang_sel li,#lang_sel a,#lang_sel span{
  height:30px !important;
  line-height:30px !important;
  font-size:14px;
}
#lang_sel > ul > li > ul{ top:31px; }

</style>
<script type="text/javascript">
jQuery(function($){
var langMenuObj = $('.submenu-languages').eq(0);
var wpmlMobile = (typeof(window.orientation) != "undefined");
$('.menu-item-language-current').eq(0).on('mouseenter',function(){
if(!wpmlMobile){langMenuObj.stop(true,true).fadeIn('fast');}
}).on('mouseleave',function(){
if(!wpmlMobile){langMenuObj.stop(true,true).fadeOut('slow');}
}).on('click',function(){
langMenuObj.stop(true,true).toggle();
});
$('.icl_lang_sel_current ').parent().click(function(){ return false; });
});
</script>

< How to insert this code? >
There are 3 example ways to insert the code. Please choose one of them. Or if you know how to customize WordPress, you can insert manually to your PHP scripts.

< Method – 1 >
Go to [ Appearance – Widgets ] of administrator menu. Please add “Text” to one valid area. Please do not enter the title, put the script only in the detail form.

< Method – 2 >
I developed a plugin STINGER3 loves WPML. This plugin do the same thing as “Method – 1”. If you find bugs, please report or tell me on Github, Twitter or Google+.

< Method – 3 >
Using WordPress plugin “Wp Insert“. It can insert HTML code to anywhere on your blog.

< Afterward >
I think each methods are not so difficult. But current version of STINGER3 is not compatible with language mo files. We can only translate with String Translation of WPML. If I have spare time, I want to develop patch for STINGER3 compatible with mo files.

< Updated on June 11, 2014 >
Fixed – It can’t handle first tap event of Android or iOS browsers. Source code on this page and the WordPress plugin on Github are updated.

Running WordPress on Android using KSWEB

< Warning >
You need the skill to set your Android’s IP address public. I don’t write the procedure about network settings, because it depends on your network hardware, software and provider. I’m sorry but please check it on another website.

< Awesome App for Android >
There is an awesome app on Google Play. The app name is KSWEB. Developer page is here. You can download from this page or Google Play.

< Requirements >
The developer of KSWEB says Android device with OS version 2.3.3 or higher and ARM-based processor is required. I will try to run KSWEB on IS05 (Japanese old Android smartphone by SHARP) and the procedure on this post is about this device. But maybe KSWEB can run on so many kinds of Android devices. Please try on any devices you have.

< Installing from Google Play >
( If you want to install from Google Play, please follow this procedure. )
You just need to go KSWEB – Google Play and install it. That’s all of installation.

< Installing from APK file >
( If you want to install from APK file, please follow this procedure. )
Before installing APK file, you need to change your Android’s setting for apps. You need to allow app installs from “unknown sources”. After that, please visit http://kslabs.ru/download/ to download the APK file of KSWEB.
20140531-174245

< Downloading WordPress >
You can choose “downloading on Android directly” or “downloading on PC and upload to Android”. I downloaded on PC from http://wordpress.org/. I installed AirDroid to my Android and visited http://web.airdroid.com/ to upload WordPress ZIP file to my Android. If you want to “download on Android directly”, you just need to visit http://wordpress.org/ by your Android browser.

< Installing ZIP extractor to your Android >
You need to extract WordPress ZIP file on your Android. I installed AndroZip™ File Manager to my Android. But there are many kinds of ZIP extractor on Google Play, you can choose any app you like.

< Extract ZIP file >
I put the ZIP file “wordpress-3.9.1.zip” on the root of SD card directory. After I extract it “wordpress” directory was automatically made.
20140531-180921

< Adding and enabling optional functions >
Please tap [ TOOLS – WebFace – Start ] to enable KSWEB Web Interface. Please tap [ TOOLS – Main – phpMyAdmin ] to add phpMyAdmin. phpMyAdmin is for making a database on MySQL for WordPress, so it’s not necessary. You can also make a database by PHP script.
20140530-002435

< Making a database by phpMyAdmin >
Please access http://localhost:8000/ by itself(Android) or http://< Android’s local IP address >:8000/ from any computer on the same network.
phpMyAdmin_login
You can login as root with no password. In my sample picture, I made a database “wordpress” for WordPress.
phpMyAdmin_table

< Change WWW root of lighttpd >
Please access http://localhost:8003/ by itself(Android) or http://< Android’s local IP address >:8003/ from any computer on the same network. It’s KSWEB Web Interface. Default user and password are both admin.
KSWEB_Web_Interface
Please click Server settings. You can edit a config file of lighttpd.
Please find a line like below.

server.document-root = “/mnt/sdcard/htdocs”

Change directory path of this line to your WordPress root.
My new setting is below.

#server.document-root = “/mnt/sdcard/htdocs”
server.document-root = “/mnt/sdcard/wordpress”

After changing config file, please reboot lighttpd or KSWEB.

< Ports of lighttpd >
Default ports setting is like below.
lighttpd_ports
If you want to change them, please edit config file.

< Don’t sleep! >
Basically Android smartphone automatically sleeps. If you want it to work 24 hours, you need to keep it away from sleeping. There are many apps to stay it awake on Google Play. Please use StayAwake, KeepScreen or another one you find on Google Play.

< Start WordPress >
Next step is same procedure of general installation of WordPress. Please access your Android with your domain name. WordPress default installation will start. Enjoy your energy‐saving WordPress life.