Namespace: Utils

dateToYMD

Returns a date as a string in YYYY-MM-DD format.

Given a date from a spreadsheet cell, returns the date in international date format (aka unix format). Used for APIs that require dates in unix format.

getDomainName

Extracts the domain from one or many URLs

getMonday

Returns the first day of the week. Useful for formatting headers.

hashToParam

Converts a hash to a query parameter

isArray

Returns true if obj is an array.

isColumn

Returns true if the array is actually in columns

isInFuture

Determines if some date is in the future.

jsonifyCol

Convert an array into a JSON string

jsonStringify

Converts an object to a JSON string

parseUrl

percentDiff

Returns the percentage difference between two numbers.

rangeToUrlString

Returns a comma separated string from a range of cells. Each one will be encoded so it can safely be used in a URL.

ratioUp

Returns a ratio of two numbers and rounds it up to decimal places as a percentage.

stripUrlScheme

Removes HTTP or HTTPS from the beginning of one or more URLs

strToArray

Converts one string into an array if it's a string

Function

=dateToYMD(d)

Parameters

Name	Type	Description
`d`	date	Date from the spreadsheet

Returns

Column	Description
`String of the date in YYYY-MM-DD format`

Converts date to YYYY-MM-DD
Converts a date to unix time format (YYYY-MM-DD)

Cells:
  A1: 11/01/2011
=dateToYMD(A1)
// Returns 2011-11-01

Function

=getDomainName(urlRange)

Parameters

Name	Type	Description
`urlRange`	string	URL

Returns

Column	Description
`Domain name as a string.`

Get the domain name
Extracts the domain name from any URL

// Returns "domain.com"
=getDomainName("http://www.domain.com/blog")

Get the domain name from a range
Extracts the domain names from a range of cells

=getDomainName(A1:A10)

Function

=getMonday(d)

Parameters

Name	Type	Description
`d`	date	Any date you want to find the start of the week for

Returns

Column	Description
`Date that is Monday (the start of the week)`

Returns the beginning of the week
Returns the beginning of the week

Cells:
   A1: 11/18/2011
=getMonday(A1)
// Nov 18th is a Friday.  This will return 11/14/2011 which is Monday.

Function

=hashToParam(hashes)

Parameters

Name	Type	Description
`hashes`	hash	Hash

Negative change gives you a negative number
Returns the percentage difference between two numbers

var queryString = hashToParam({
   "key"     : "1234abcd5678",
   "param"   : "value",
   "param2"  : "value2"
}); // => "key=1234abcd5678¶m=value¶m2=value2"

Function

=isArray(obj)

Parameters

Name	Type	Description
`obj`	obj	Some object

Returns

Column	Description
`Returns true if the object is an array`

isArray([1, 2, 3]); // => true
isArray("hello world"); // => false

Function

=isColumn()

Parameters

// myArray is A1:A3
isColumn(myArray); // => true
// myArray is A1:C1
isColumn(myArray); // => false
// myArray is A1
isColumn(myArray); // => false

Function

=isInFuture(date)

Parameters

Name	Type	Description
`date`	date	The starting date

Returns

Column	Description
`True if the date is in the future, false if it's not.`

Check if date is in the future
Returns true if the date is > today

  A1: 11/01/2020
=isInFuture(A1) // true

Function

=jsonifyCol(inArray)

Parameters

Name	Type	Description
`inArray`	array	Range of cells

Returns

Column	Description
`JSON string of the first column in an array`

Array to JSON
Returns a JSON string of the array that was passed in.

var myArray = [[1, 2], [3, 4]];
jsonifyCol(myArray); // => '[1, 3]'
jsonifyCol("string"); // => '["string"]';

Function

=jsonStringify(obj)

Details

Credit: https://gist.github.com/754454

Parameters

Name	Type	Description
`obj`	object	Object you want to make in to a JSON string

Returns

Column	Description
`JSON string`

Function

=parseUrl(URL)

Details

Separates a URL into seperate pieces (such as the domain name, the path, or the query string)

Columns returned

url: http://www.ora.com:80/goodparts?q#fragment
scheme: http
slash: //
host: www.ora.com
port: 80
path: goodparts
query: q
hash: fragment

Credit: JavaScript: The Good Parts

Parameters

Name	Type	Description
`URL`	string	Url to parse

=parseUrl("http://www.ora.com:80/goodparts?q#fragment");

Function

=percentDiff(oldNumber, newNumber)

Parameters

Name	Type	Description
`oldNumber`	number	Old number
`newNumber`	number	New number

Returns

Column	Description
`Percentage difference from the old number to the new one.`

Positive change gives you a positive number
Returns the percentage difference between two numbers

//
// A1: Yesterday's Visits
// A2: 100
// B1: Today's Visits
// B2: 200
// C1: Percentage difference:
// C2:
   =percentDiff(A2, B2) // 100%

Negative change gives you a negative number
Returns the percentage difference between two numbers

// A1: Yesterday's Visits
// A2: 100
// B1: Today's Visits
// B2: 75
// C1: Percentage difference:
// C2:
   =percentDiff(A2, B2) // -25%

Function

=rangeToUrlString(range)

Parameters

Name	Type	Description
`range`	string	A range of cells

Returns

Column	Description
`Query string`

Convert to URL string
Turns an array or range into a comma delimited query string

Cells:
   A1: domain.com
   A2: domain.com/blog
=rangeToUrlString(A1:A2)
// => "domain.com,domain.com%2Fblog"

Function

=ratioUp(numerator, denominator, decimalPlaces)

Parameters

Name	Type	Description
`numerator`	number	Numerator
`denominator`	number	Denominator
`decimalPlaces`	integer	Number of decimal places

Returns the ratio of two numbers as a percentage
Returns the beginning of the week

=ratioUp(8, 43, 2)
// Returns 0.19

Function

=stripUrlScheme(url)

Parameters

Name	Type	Description
`url`	string	URL

Returns

Column	Description
`URL string without the protocol`

From a range of URLs
Removes HTTP/HTTP from a range of URLs

A1: http://www.domain.com
A2: https://www.anotherdomain.com
B1: =stripUrlScheme(A1:A2)

Function

=strToArray(obj)

Parameters

Name	Type	Description
`obj`	obj	Object to convert to array

Returns

Column	Description
`Array`

From a string
Returns a single dimentional array if given a string

strToArray("hello world"); // => ["hello world"]

From a array
Does nothing if it's already an array

strToArray(["an array"]); // => ["an array"]

Utilities

dateToYMD

Function

Parameters

Returns

Examples

Converts date to YYYY-MM-DD Converts a date to unix time format (YYYY-MM-DD)

getDomainName

Function

Parameters

Returns

Examples

Get the domain name Extracts the domain name from any URL

Get the domain name from a range Extracts the domain names from a range of cells

getMonday

Function

Parameters

Returns

Examples

Returns the beginning of the week Returns the beginning of the week

hashToParam

Function

Parameters

Examples

Negative change gives you a negative number Returns the percentage difference between two numbers

isArray

Function

Parameters

Returns

Examples

isColumn

Function

Parameters

Examples

isInFuture

Function

Parameters

Returns

Examples

Check if date is in the future Returns true if the date is > today

jsonifyCol

Function

Parameters

Returns

Examples

Array to JSON Returns a JSON string of the array that was passed in.

jsonStringify

Function

Details

Parameters

Returns

Examples

parseUrl

Function

Details

Columns returned

Parameters

Examples

percentDiff

Function

Parameters

Returns

Examples

Positive change gives you a positive number Returns the percentage difference between two numbers

Negative change gives you a negative number Returns the percentage difference between two numbers

rangeToUrlString

Function

Parameters

Returns

Examples

Convert to URL string Turns an array or range into a comma delimited query string

ratioUp

Function

Parameters

Examples

Returns the ratio of two numbers as a percentage Returns the beginning of the week

stripUrlScheme

Function

Parameters

Returns

Converts date to YYYY-MM-DD
Converts a date to unix time format (YYYY-MM-DD)

Get the domain name
Extracts the domain name from any URL

Get the domain name from a range
Extracts the domain names from a range of cells

Returns the beginning of the week
Returns the beginning of the week

Negative change gives you a negative number
Returns the percentage difference between two numbers

Check if date is in the future
Returns true if the date is > today

Array to JSON
Returns a JSON string of the array that was passed in.

Positive change gives you a positive number
Returns the percentage difference between two numbers

Negative change gives you a negative number
Returns the percentage difference between two numbers

Convert to URL string
Turns an array or range into a comma delimited query string

Returns the ratio of two numbers as a percentage
Returns the beginning of the week

From a range of URLs
Removes HTTP/HTTP from a range of URLs

From a string
Returns a single dimentional array if given a string

From a array
Does nothing if it's already an array