Turn array of strings into array of dates in Google Apps Script

Question

I have a Google Sheets spreadsheet. In Column B, I have a list of strings that are either dates or ranges of dates in the format month/date. For example:

7/26
7/27-7/31
8/1
8/2
8/3-8/5

I want to create an array with the first date on the left and the second date (if any) on the right. If there's no second date, it can be left blank. This is what I want:

[7/26,]
[7/27,7/31]
[8/1,]
[8/2,]
[8/3,8/5]

I've tried:

var r = 'B'
var dateString = sheet.getRange(dateColumns[r] + '1:' + dateColumns[r] + lastRow.toString()).getValues();
var dateArr = Utilities.parseCsv(dateString, '-');

But that just keeps concatenating all values. Also if it's possible to put the output in a date format that would be great too.

Serge insas · Accepted Answer · 2014-07-26 09:15:15Z

1

This was a funny exercise to play with...

Here is a code that does what you want :

function test(){
  convertToDateArray('7/26,7/27-7/31,8/1,8/2,8/3-8/5');
}

function convertToDateArray(inputString){
  if(typeof(inputString)=='string'){inputString=inputString.split(',')}; // if input is a string then split it into an array using comma as separator
  var data = [];
  var datesArray = [];
  for(var n in inputString){
    if(inputString[n].indexOf('-')==-1){inputString[n]+='-'};// if only 1 field add an empty one
    data.push(inputString[n].split('-'));// make it an array
  }
  Logger.log(data);//check
  for(var n in data){
    var temp = [];
    for(var c in data[n]){
      Logger.log('data[n][c] = '+ data[n][c]);
      var date = data[n][c]!=''? new Date(2014,Number(data[n][c].split('/')[0])-1,Number(data[n][c].split('/')[1]),0,0,0,0) : '';// create date objects with right values
      Logger.log('date = '+date);//check
      temp.push(date);
    }
    datesArray.push(temp);//store output data in an array of arrays, ready to setValues in a SS
  }
  Logger.log(datesArray);
  var sh = SpreadsheetApp.getActive().getActiveSheet();
  sh.getRange(1,1,datesArray.length,datesArray[0].length).setValues(datesArray);
}

Logger result for datesArray :

[[Sat Jul 26 00:00:00 GMT+02:00 2014, ], [Sun Jul 27 00:00:00 GMT+02:00 2014, Thu Jul 31 00:00:00 GMT+02:00 2014], [Fri Aug 01 00:00:00 GMT+02:00 2014, ], [Sat Aug 02 00:00:00 GMT+02:00 2014, ], [Sun Aug 03 00:00:00 GMT+02:00 2014, Tue Aug 05 00:00:00 GMT+02:00 2014]]

edited Jul 26, 2014 at 9:15

answered Jul 26, 2014 at 9:09

Serge insas

46.9k7 gold badges113 silver badges135 bronze badges

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

1 Comment

johnvan Over a year ago

ca check that URL pleas: stackoverflow.com/questions/69989580/…

Collectives™ on Stack Overflow

Turn array of strings into array of dates in Google Apps Script

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related