This is the second part of the guide on how to implement custom functions into Google Drive Spreadsheets.

UPDATE: If you would like to easily update calculated values from this custom function than you have to go the following way: http://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-spreadsheet – I just followed mentioned guide there and I have an extra button in the menu now, which allows me easily refresh all values which were calculated by my custom function.

I have been receiving many questions about how exactly to use the script mentioned in the previous part of this guide so I decided, that it would be better to write it down incl. screenshots.

1. Open some of Your already created spreadsheets or create a blank one

2. Using the main menu at the top choose: Tools -> Script manager

3. In this dialog click the New button and it will guide You to the Scripts editor

4. Paste following code into the editor, give some name to the script (top left corner input field), and Save it (Ctrl+S)

function sumWhereIsFontWeight(rangeSpecification) {

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange(rangeSpecification);

var x = 0;

for (var i = 1; i <= range.getNumRows(); i++) {
for (var j = 1; j <= range.getNumColumns(); j++) {

var cell = range.getCell(i, j);

if(cell.getFontWeight() == 'bold')
x += parseFloat(cell.getValue());
}
}

return x;
}

4. Get back to spreadsheet window and reload Your Scrip Manager dialog (circle arrow icon in top right corner) and You should see Your newly created script

5. Now You should be able to use this function in Your spreadsheet. To make it more clear, I added to the A column demo of what I added into columns in the B column (of course without those slashes at the beginning “//”). Here is screenshot of demo:

I got into multiple guides on stackoverflow and different discussion forums, but only one solution gave me instant answer on this.

There are multiple ways, how you can find such an information:

• using command line and netstat command
• using Resource monitor in Windows (Win+Esc, switch to Performance tab and click Resource monitor in bottom – in new window click Network tab and open Listening ports at bottom)
• using NET command in cmd

The best and fastest way seems to me NET command, because this was only way how I was able to detect exact names of services using this port (other ways did not provide me exact name of service, mostly only info that it is being run by “System”):

NET stop HTTP

This will give you following output and also option to stop those services immidiately:

C:\Users\someuser>NET stop HTTP
The following services are dependent on the HTTP Service service.
Stopping the HTTP Service service will also stop these services.

SSDP Discovery
 Print Spooler
 BranchCache

Do you want to continue this operation? (Y/N) [N]: n

Hope, that this will help to someone else too

Mage::getBaseUrl() => Gets base url path e.g. http://my.website.com/

Mage::getBaseUrl(‘media’) => Gets MEDIA folder path e.g. http://my.website.com/media/

Mage::getBaseUrl(‘js’) => Gets JS folder path e.g. http://my.website.com/js/

Mage::getBaseUrl(‘skin’) => Gets SKIN folder path e.g. http://my.website.com/skin/

Get DIRECTORY paths (physical location of your folders on the server) – Relative URL Path

Mage::getBaseDir() => Gives you your Magento installation folder / root folder e.g. /home/kalpesh/workspace/magento

Mage::getBaseDir(‘app’) => Gives you your Magento’s APP directory file location e.g. /home/kalpesh/workspace/magento/app

Mage::getBaseDir(‘design’) => Gives you your Magento’s DESIGN directory file location e.g. /home/kalpesh/workspace/magento/design

Mage::getBaseDir(‘media’) => Gives MEDIA directory file path

Mage::getBaseDir(‘code’) => Gives CODE directory file path

Mage::getBaseDir(‘lib’) => Gives LIB directory file path

Get Current URL – whole URL path

Mage::helper(‘core/url’)->getCurrentUrl()

base Mage::getBaseDir()
Mage::getBaseDir(‘base’) /var/www/magento/
app Mage::getBaseDir(‘app’) /var/www/magento/app/
code Mage::getBaseDir(‘code’) /var/www/magento/app/code
design Mage::getBaseDir(‘design’) /var/www/magento/app/design/
etc Mage::getBaseDir(‘etc’) /var/www/magento/app/etc
lib Mage::getBaseDir(‘lib’) /var/www/magento/lib
locale Mage::getBaseDir(‘locale’) /var/www/magento/app/locale
media Mage::getBaseDir(‘media’) /var/www/magento/media/
skin Mage::getBaseDir(‘skin’) /var/www/magento/skin/
var Mage::getBaseDir(‘var’) /var/www/magento/var/
tmp Mage::getBaseDir(‘tmp’) /var/www/magento/var/tmp
cache Mage::getBaseDir(‘cache’) /var/www/magento/var/cache
log Mage::getBaseDir(‘log’) /var/www/magento/var/log
session Mage::getBaseDir(‘session’) /var/www/magento/var/session
upload Mage::getBaseDir(‘upload’) /var/www/magento/media/upload
export Mage::getBaseDir(‘export’) /var/www/magento/var/export

1. Start Git bash command line at the location where You want have Your repository cloned.
2. Copy clone command from Your repository web page – like: git clone git@bitbucket.org:username/repositoryName.git ./desiredTargetFolderName
3. first paste following command into bash: ssh -vT git@bitbucket.org
4. Than run clone command and it should work

Hope it will help someone.

Update:

If You`re still having problems with pulling data  from Your repository (because of permission), check Your home ssh folder, if it contains correct public key file, usually it is:

~/.ssh/id_rsa.pub

function sumWhereIsFontWeight(rangeSpecification) {

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange(rangeSpecification);

var x = 0;

for (var i = 1; i <= range.getNumRows(); i++) {
for (var j = 1; j <= range.getNumColumns(); j++) {

var cell = range.getCell(i, j);

if(cell.getFontWeight() == 'bold')
x += parseFloat(cell.getValue());
}
}

return x;
}

I also prepared function for the opposite situation, and called it sumWhereIsNotFontWeight, here is its code:

function sumWhereIsNotFontWeight(rangeSpecification) {

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange(rangeSpecification);

var x = 0;

for (var i = 1; i <= range.getNumRows(); i++) {
for (var j = 1; j <= range.getNumColumns(); j++) {

var cell = range.getCell(i, j);

if(cell.getFontWeight() != 'bold')
x += parseFloat(cell.getValue());
}
}

return x;
}

UPDATE (2013/11/15):

I have created new article where I explain more in detail, how to implement this script into Google Drive Spreadsheet, check it here.

SELECT * FROM table WHERE id IN (1,5,8,73,5,4,88)

This will return rows ordered by its primary key (in my case column id). Here is updated query, which will keep order of items listed in WHERE IN clause:

SELECT * FROM table WHERE id IN (1,5,8,73,5,4,88) ORDER BY FIELD(id,1,5,8,73,5,4,88)

Please note first item in brackets behind ORDER BY – there should be set same column, which was used at WHERE IN clause.

UPDATE: There was missing function called FIELD after ORDER BY – code is fixed now.

1. on the left side choose You app
2. than on the top right click on “Edit App” button
3. On the left should be choosen: Settings -> Basic
4. Go down into the section called: “Select how your app integrates with Facebook”
5. Find setup for Page Tab and check if you have set correct width of used app layout (mine was 520px)
6. Save changes, if anything changed
7. Than on the left choose Settings -> Advanced
8. In the section “Canvas Settings” should be following setup for canvas height: fluid

If above described setup is ok, than we have to fix it in our application. It is simple, with two code snippets. Just add following before </body> tag (If You are not using Facebook init script already):

<script type="text/javascript" src="http://connect.facebook.net/en_US/all.js"></script><script type="text/javascript">// <!&#91;CDATA&#91;
	FB.init({
		appId : '399179393429469',
		status : true, // check login status
		cookie : true, // enable cookies to allow the server to access the session
		xfbml : true // parse XFBML
	});
// &#93;&#93;></script>

And put this code just before closing tag :

  <script type="text/javascript">
	window.fbAsyncInit = function() {
		FB.Canvas.setSize();
	}
	function sizeChangeCallback() {
		FB.Canvas.setSize();
	}
	</script>

Now, If your page is coded correctly into desired width, You should not see scrollbars anymore in it.
Hope it helps.

The easiest way, how to get his/her UID  is to load user`s facebook profile and right click his/her main profile image and select Inspect element in submenu. It will open Your Firebug or browser/DOM inspector (new Firefox or Chrome). In the inspector, You should see and URL of image. What I found out, is, that user`s facebook profile ID is second number in the photo name. Mine was like this: 157184_1317732574_574480779_n.jpg

If You are using Facebook timeline, You could check Facebook profile ID also from the bigger profile picture. In my case it was third number: 425232_3326820732162_1317732574_3347250_1064723857_n.jpg

The easiest way, how to check, if the number is correct is to add selected number behind this URL and load it in the broswer:

https://www.facebook.com/profile.php?id=AddYourNumberHere

If You will want to add more moderators or admins into Your facebook app using these IDs, just find out all of them and insert them into text input separated with comma without spaces. Facebook will handle this numbers and it will add selected profiles into the list of moderators automatically (than just hit Save button at the bottom of the moderation setup dialog/page).

Hope it will help You

Here are two simple SQL queries solving both:

Query for generate random date of birth in whole table

UPDATE

people

SET

date_of_birth=CONCAT_WS('-',(FLOOR( 1900 + RAND( ) *100)),(FLOOR( 1 + RAND( ) *12 )),(FLOOR( 1 + RAND( ) *28 )))

Query for selecting date of birth and calculation of age in MySQL:

SELECT
date_of_birth,
DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(date_of_birth, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(date_of_birth, '00-%m-%d')) AS age
FROM people

When You download and start installation of the client (mine was: vpnclient-winx64-msi-5.0.07.0290-k9.exe), You can start installation, but in one of the first steps will error occur:

installation ended prematurely because of an error

Fix which worked for me was following:

1. Delete whole registry key (better do backup first, by right-click this key and select Export): HKEY_CURRENT_USER\SOFTWARE\Classes\Wow6432Node\CLSID\{B54F3741-5B07-11CF-A4B0-00AA004A55E8}
2. Start commnad line as an administrator and type: regsvr32 c:\windows\syswow64\vbscript.dll
3. Start installation again and it should work
4. Restart your machine after install when promted