Google Sheets – Parsing Timestamp in Google Sheets with Script

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

I have a spreadsheet with a form that timestamps every time the form is submitted. I'm writing a script to change the background color of the timestamped cell if it is more than seven minutes after a certain time.

Is there a way to compare time without comparing date in the API?

Should I convert the timestamp to another format (a string or something) to compare it?

_{I need to highlight the cell if it's after 6:07pm on any given day.}

Best Answer

The following piece of code will set the background color of the entire row:

function onFormSubmit(e) {
  // get data and make it a 1D array
  var time = e.range.getValues()[0];

  // get the minutes and hours of the timestamp (time[0]) 
  var minutes = time[0].getMinutes(), hours = time[0].getHours();

  // calculate the total amount of minutes
  var totalMin = (hours * 60) + minutes;

  // do the logic (thanks to geekzspot for the 1087)
  if(totalMin > 1087) {
    e.range.setBackgroundColor('yellow');
  } else {
    e.range.setBackgroundColor('red'); 
  }  
}

UPDATE
This code will set only the back ground color of the timestamp:

function onFormSubmit(e) {
  // get active spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();

  // get data and make it a 1D array
  var time = e.range.getValues()[0];

  // get row index
  var row = e.range.getRowIndex();

  // get the minutes and hours  
  var minutes = time[0].getMinutes(), hours = time[0].getHours();

  // calculate the total amount of minutes
  var totalMin = (hours * 60) + minutes;

  // set range to timestamp
  var range = sh.setActiveCell("A"+row);

  // do the logic (thanks to geekzspot for the 1087)
  if(totalMin > 1087) {
    range.setBackgroundColor('yellow');
  } else {
    range.setBackgroundColor('red'); 
  }  
}

Add this script, via the script editor, to your spreadsheet and set the trigger:
enter image description here

Here's the form: Test For Web Applications and here's the sheet

Short answer

Google Sheets pass custom function arguments as values, not as objects, in this case not as a cell/range.

Workaround

Use SpreadsheetApp.getActiveRange().getFormula() to get the cell formula and extract the reference from it.

Example

Note: Custom functions recalculates when their arguments changes their values. Changing the background of the referenced cell will not make that Google Sheets recalculate them.

function isColor(input) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  try {
    var rangeA1Notation = formula.match(/=\w+\((.*)\)/i)[1].split('!');
  } catch(e) {
    throw new Error('The reference isn\'t valid');
  }
  //
  Logger.log(rangeA1Notation);
  var cell = sheet.getRange(rangeA1Notation);
  var color = cell.getBackground();
  Logger.log(cell);
  color = cell.getBackground(); 
  Logger.log(color);
  var result = false;
  if (color == '#ffffff') {
      result = false;}
      else {
      result = true;
      }
  return result;
}

Explanation

In the Google Sheets / Google Apps Script argot, user-defined functions are called custom functions.

From the custom functions official documentation:

Data types

Google Sheets stores data in different formats depending on the nature of the data. When these values are used in custom functions, Apps Script treats them as the appropriate data type in JavaScript. These are the most common areas of confusion:

Times and dates in Sheets become Date objects in Apps Script. If the spreadsheet and the script use different time zones (a rare problem), the custom function will need to compensate.

Duration values in Sheets also become Date objects, but working with them can be complicated.

Percentage values in Sheets become decimal numbers in Apps Script. For example, a cell with a value of 10% becomes 0.1 in Apps Script.

IMHO the documentation should include a note regarding cell/range references as the Spreadsheet service includes Range as class.

Google-sheets – Highlight the admin responses rows

Apply conditional formatting to the range A2:C with custom formula

=regexmatch($B2, "^admin$")

The idea is that the formula should be written as it applies to the upper left corner of the formatted range, in this case A2. It will be automatically adjusted for other cells: for example, cell C6 will refer to B6, because row reference is relative while column reference is absolute.

Using regexmatch with the pattern "^admin$" ensures complete case-sensitive string match; ^ means the beginning of string and $ means its end.

If you want case-insensitive comparison, the formula is simply

 =$B2 = "admin"

Best Answer

Related Solutions

Google Sheets – Fix ‘Range Not Found’ Error in User-Defined Function

Short answer

Workaround

Example

Explanation

Data types

Google-sheets – Highlight the admin responses rows

Related Topic