Set Value in Date format in first Empty Column : Google Script

Question

I have a column in which dates are saved in string format like this: "Tuesday, 18th November(11:00)" I want to take this string date and save its equivalent date in Date format corresponding to its row in new column (first empty column in sheet), so that I can later compare that date with current date.

I have written this function and I am in test phase. However I have two questions :

1) This function is not inserting value in corresponding row in new column.

2) Since setValue is Object type it will not save value in Date type, for me setDay, set Month methods are not working (may be because of wrong object).

Here is my code:

function replaceStringDate(e) {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;
var lastRow = SpreadsheetApp.getActiveSheet().getLastRow();
var lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
var dataRange = sheet.getRange(startRow,2,lastRow,12);
var values = dataRange.getValues();
var index = dataRange.getRowIndex();


  for(i=0;i<values.length;++i){ 

            var lastCell = sheet.getRange(index,14);  
            var appointmentDateFirst = values[i][8] ; 

             if (appointmentDateFirst == "Thursday, 18th November (11:00 to 12:00)") {lastCell.setValue('18/11/2011');} 

        index=index+1;


    } //end for loop 

} //end function

First Question issue is solved, script is inserting value correctly. Still looking for solution related to second question. — Basit
– Basit, Commented Oct 29, 2014 at 18:55

Serge insas · Accepted Answer · 2014-10-29 20:04:49Z

0

here is a piece of code to start with, I played with string manipulation and regex... It's kind of fragile and needs to be improved to handle different cases but the idea is working... I'm sure it can be done more efficiently with only regex but this string approach was easier...

function convertToDateTest(){
  Logger.log(convertToDate("Tuesday, 18th November(11:00)"))
}

function convertToDate(str){
  Logger.log(str.substring(str.indexOf(', ')+1,str.indexOf('(')).replace('th','')+' '+new Date().getFullYear());
  var date = new Date(str.substring(str.indexOf(', '),str.indexOf('(')).replace('th','')+' '+new Date().getFullYear());
  var time = str.match(/((([0-1]?[0-9])|([2][0-3])):)([0-5][0-9])/g);
  Logger.log(time);
  new Date(date).setHours(time[0]);
  return date;
}

enter image description here

answered Oct 29, 2014 at 20:04

Serge insas

46.9k7 gold badges113 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Serge insas Over a year ago

Feel free to show your changes and consider accepting the answer if you think it's a good one :-)

Collectives™ on Stack Overflow

Set Value in Date format in first Empty Column : Google Script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related