Set the value of empty cells in a column to date in adjacent column, as a string, with Google Script

Question

I'm using Sheetrock.js to query and display data from a Google Sheet, which is attached to a form. The problem is that I need to be able to search the sheet by the Timestamp column, and I can only do this with Sheetrock by converting the Timestamp to plain text (otherwise the query results in an empty table). Pre-formatting the column as plain text doesn't work, since the formatting gets overwritten by any new form submissions.

I've added a second column to the right of the Timestamp with a view to displaying the date-time here as plain text, and querying the sheet with this column instead. I was able to write a script that finds the last row in the sheet (presumably the newest), converts the Timestamp to a string, and writes it to the next column with a form submit trigger; however because of the way I've scripted the form (multiple rows are sent in with each submit, all with the same timestamp), this leaves a lot of empty cells in the column. For example, I'll receive three new submissions with the same timestamp, but only the last one runs the function because technically, all three submissions share the same trigger.

So now (TL;DR), I'm trying to write a script to look for empty cells in column B, find the adjacent cell in column A (Timestamp), convert this to a string, and write the result in column B. I've tried a lot of different methods but my proficiency in Google Script is not where I'd like it to be. This is what I had working before:

function lastValue(column) {
  var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
  var values = SpreadsheetApp.getActiveSheet().getRange(column + "1:" + column + lastRow).getValues();

  for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
  return values[lastRow - 1];
}

function lastCell(column) {
  var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
  var values = SpreadsheetApp.getActiveSheet().getRange(column + "1:" + column + lastRow).getValues();

  for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
  return lastRow;
}

function formatDate () { //Triggered on form submit
   var value = lastValue("A");

   var string = value.toString();
   var cell = "B" + lastCell("A");

   SpreadsheetApp.getActiveSheet().getRange(cell).setValue(string); 
}

...but I haven't been able to make much progress in fixing it. Any insight or pointers would be much appreciated!

Ritz Ritz · Accepted Answer · 2017-08-08T19:17:50

The best option is to convert the format of first column to text when the form is submitted.

function OnSubmit(e){
  var sheetDatabase = SpreadsheetApp.openById("<<SPREADSHEET ID>>").getSheetByName("Sheet1")
  sheetDatabase.getRange("A:A").setNumberFormat("@");
}

Set the value of empty cells in a column to date in adjacent column, as a string, with Google Script

1 Answers