CODEWITHSUNDEEP
google-apps-scriptgoogle-apps
Basit
Basit

Reputation: 41

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

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

Upvotes: 0

Views: 1174

Answers (1)

Serge insas
Serge insas

Reputation: 46792

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

Upvotes: 0

Related Questions