Google Script snippets

A selection of simple javascript snippets and functions that I’ve used in Google Scripts.

I’ll likely add to these over time but, at present, the list includes:

  1. Clean string
  2. Convert a two dimensional array to a one dimensional array
  3. Get first empty column in a Google Sheet
  4. Get first empty row in a Google Sheet
  5. Get last row in a Google Sheet
  6. Replace html entities in each array element
  7. Calculate if paramater is a letter
  8. Test if input is a letter or a number
  9. Get today’s date

Source code on Github.

1. Clean string


/**
* Strips spaces and line breaks from string
*
* @param {String} s
* @return {String} stripLineBreaks
*/
function cleanString(s) {

  var stripSpaces = s.replace(/\s+/g, '');
  var stripLineBreaks = stripSpaces.replace(/\r?\n|\r/g, '');

  return stripLineBreaks;
}

2. Convert a two dimensional array to a one dimensional array


/**
* Converts a two dimensional array to a one dimensional array
*
* @param {Array} twoDimArray
*
* @return {Array} oneDimArray
*/
function convertTwoToOneDimArray(twoDimArray) {
  "use strict";
  var oneDimArray, outputArrayCount, i, k, j, m;
  oneDimArray = [];
  outputArrayCount = 0;
  for (i = 0, k = twoDimArray.length; i < k; i = i + 1) {

    for (j = 0, m = twoDimArray[i].length; j < m; j = j + 1) {

      oneDimArray[outputArrayCount] = twoDimArray[i][j];
      outputArrayCount = outputArrayCount + 1;

    }
  }

  return oneDimArray;

}

3. Get first empty column in a Google Sheet


/**
* Gets first empty column from spreadsheet object
*
* @param {Object} sheet
* @return {Number} total
*/
function getFirstEmptyCol(sheet) {

  /*source: http://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row-google-apps-script*/
  var range = sheet.getDataRange();
  var values = range.getValues();
  var valueLength = values.length;
  var count = 0;
  var total = 0;
  for (var row=0; row<valueLength; row+=1) {
   for (var col=0; col<values[row].length; col+=1) { 
     count++; 
     if (count > total) {
       total = count;
     }
   }
   count = 0;
  }
return (total+1);
}

4. Get first empty row in a Google Sheet


/**
* @desc Get number of first empty row of spreadsheet
* @param ss (object)
* @return count (number)
* @source: http://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row-google-apps-script
*/
function getFirstEmptyRow(ss) {
  "use strict";
  var column, values, count;
  column = ss.getRange('A:A');
  values = column.getValues();
  count = 0;
  while ( values[count][0] !== "" ) {
    count = count + 1;
  }
  count = count + 1; /*because index starts at 0*/
  return count;
}

5. Get last row in a Google Sheet


/**
* Get last row from column
*
* @param {object} sheet
* @param {number} columnNum
* @param {number} lastRow
*
* @return {Number}
*/
function getLastRowInCol(sheet, columnNum, lastRow) {
  var data = sheet.getRange(1, columnNum, lastRow, 1).getValues();
  var lastRowInCol = 0;
  for (i = lastRow -1; i > 0; i--) {
    if (data[i] != "") {

    return i+1;
   }
  }
}

6. Replace html entities in each array element


/**
* @desc replaces html entities in each array element
* @param outputArray (array)
* @return outputArray (array)
* source: http://css-tricks.com/snippets/javascript/htmlentities-for-javascript/
*/
function htmlEntities(outputArray) {
  "use strict";
  var outputArrayElement, i, j;
  outputArrayElement = "";
  for (i = 0, j = outputArray.length; i < j; i = i + 1) {
    if (typeof outputArray[i] === "string") {
     outputArrayElement = outputArray[i];
     outputArrayElement.replace(/&/g, '&').replace(//g, '>').replace(/"/g, '"');
     outputArray[i] = outputArrayElement;
    }
  }
  return outputArray;
}

7. Calculate if parameter is a letter


/**
* Calculates if parameter is a letter
*
* @param {String} text
* @return {Boolean}
*/
function isLetter(text) {
  var letters = /^[A-Za-z]+$/;
  if (text.match(letters)) {
    return true;
  } else {
    return false;
  }
}

8. Calculate if parameter is a letter or a number


/**
* Calculates if parameter is a letter or number
*
* @param {String} text
* @return {Boolean}
*/
function isLetterOrNumber(text) {
  var characters = /^[A-Za-z0-9]+$/;
  if (text.match(characters)) {
    return true;
  } else {
    return false;
  }
}

9. Get today’s date


/**
* @desc Returns today's date in (d)d/(m)m/yyy format
* @return today (string)
*/
function todayDate () {
  "use strict";
  var today, day, month, year;
  today = new Date();
  day = today.getDate();
  month = today.getMonth()+1; /*January is 0*/
  year = today.getFullYear();
  today = day+'/'+month+'/'+year;
  return today;
}