Google Drive - Google Apps Script

UPDATE no. 2: Implementation Guide: SUM values if font is bold – Google Drive Spreadsheet

NEW: Screen recording of the solution at the end of this post.

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

UPDATE: If you would like to easily update calculated values from this custom function, than you have to go 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 extra button in menu now, which allows me easily refresh all values which were calculated by my custom function.

I have been receiving many questions how exactly use the script mentioned in previous part of this guide so I decided, that it would be better to write it down incl. screenshots.
Continue reading UPDATE no. 2: Implementation Guide: SUM values if font is bold – Google Drive Spreadsheet

How detect services running on 80 port at Windows 10

I was facing issue, that I needed to install web server using 80 port on my Windows 10 workstation, but I found out, that port is being blocked on fresh installed system. It took me some time to figure out, what exactly is using this port.

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 🙂

 

Get magento system paths and URLs dynamically

Get URL paths of your magento folder structure – Absolute URL Path

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

UPDATE: FATAL ERROR while cloning from Bitbucket private repository

FATAL ERROR: Disconnected: No supported authentication methods available (server sent: publickey)
If this is error, which You are getting form Bitbucket server while trying to clone Your private repository, you can try following:

  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

Google Drive - Google Apps Script

Script: SUM values if font is bold – Google Drive Spreadsheet

I was working on some calculations using Google Drive Spreadsheet.
Than I found out, that I would need SUM values only from cells, which has bold font… Than I found out, that there is no simple way, how to do that with basic functions included in Spreadsheet application. That was the moment, when I found out, that there is option create a custom fuction by my own needs using Google Apps Script!
Continue reading Script: SUM values if font is bold – Google Drive Spreadsheet

UPDATE: Keep order of rows while using WHERE IN () – MySQL

I was just solving issue, where I needed to keep an order of items returned from the MySQL database listen in the WHERE IN clause.
Original query:

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.

Get rid of scrollbars in Facebook canvas application

First of all, make sure, that you have correct settings in Your Facebook app setup. To check it, go to: https://developers.facebook.com/apps

  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

How get Facebook profile ID (uid) when using vanity URL

When You are working with facebook apps, you do need sometimes to add administrators or moderators to Your application. If selected person is not in Your friend list, You cannot add him to the list using auto-suggest box. Thats the moment, when You will need to know user`s facebook profile id (uid). Real problem is coming when user is using Facebook vanity URL for his/her profile (like www.facebook.com/some.selected.vanity.url). Continue reading How get Facebook profile ID (uid) when using vanity URL

Generate random birth date in MySQL table and get Age by select

Today I was solving generating of random data for testing and manipulation with the date of the birth.

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