How to change time format in JavaScript?

Question

I am trying to make a timeline using google visualization. I found in documentation sample code and it seems like if I will only change the row values it should be fine.

My data looks like this TimeLineData So you can see that I have everything I need to do this.

My problem is how Can I change dates from column C and D

from this: [Thu Oct 29 09:05:00 GMT+09:00 2020] to this: new Date(2020,10,29,9,5,0),

It is needed to put into the row which should look like this: (from the example)

[ 'Magnolia Room', 'Beginning JavaScript',       new Date(0,0,0,12,0,0),  new Date(0,0,0,13,30,0) ],

I guess you can use array from the whole range but I have no idea how can I process the whole array so I divided it into 4 parts. I don't know if its correct.

Data in spreadsheet are in the same order as it should be in the row

 [ 'Machine', 'Product',       new Date(start),  new Date(end) ],

Any advice will be helpful!

This is what I got:

  <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

<script type="text/javascript">

function timelineData(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName("Array");
  const srcValues = srcSheet.getRange(2, 1, srcSheet.getLastRow(), srcSheet.getLastColumn()).getValues();
  
  
  const machine = srcSheet.getRange(2, 1, srcSheet.getLastRow()).getValues();
  const order = srcSheet.getRange(2, 2, srcSheet.getLastRow()).getValues();

  const startDate = srcSheet.getRange(2, 3, srcSheet.getLastRow()).getValues();
  const endDate = srcSheet.getRange(2, 4, srcSheet.getLastRow()).getValues();
        
  

}

  google.charts.load("current", {packages:["timeline"]});
  google.charts.setOnLoadCallback(drawChart);
  function drawChart() {

    var container = document.getElementById('example3.1');
    var chart = new google.visualization.Timeline(container);
    var dataTable = new google.visualization.DataTable();
    dataTable.addColumn({ type: 'string', id: 'Machine' });
    dataTable.addColumn({ type: 'string', id: 'Order' });
    dataTable.addColumn({ type: 'date', id: 'Start' });
    dataTable.addColumn({ type: 'date', id: 'End' });
    dataTable.addRows([
       [ 'Magnolia Room', 'Beginning JavaScript',       new Date(0,0,0,12,0,0),  new Date(0,0,0,13,30,0) ],
      [ 'Magnolia Room', 'Intermediate JavaScript',    new Date(0,0,0,14,0,0),  new Date(0,0,0,15,30,0) ],
      [ 'Magnolia Room', 'Advanced JavaScript',        new Date(0,0,0,16,0,0),  new Date(0,0,0,17,30,0) ],
      [ 'Willow Room',   'Beginning Google Charts',    new Date(0,0,0,12,30,0), new Date(0,0,0,14,0,0) ],
      [ 'Willow Room',   'Intermediate Google Charts', new Date(0,0,0,14,30,0), new Date(0,0,0,16,0,0) ],
      [ 'Willow Room',   'Advanced Google Charts',     new Date(0,0,0,16,30,0), new Date(0,0,0,18,0,0) ]

    ]);

    chart.draw(dataTable);
  }
</script>

<div id="example3.1" style="height: 200px;"></div>

So I edited code with the code from @Marios and now it looks like this:

     function drawChart() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName("timeLineData");
  const srcValues = srcSheet.getRange('A2:D'+srcSheet.getLastRow()).getValues();
  const newAr = srcValues.map(r=>[r[0],r[1],new Date(r[2]),new Date(r[3])]);
    var container = document.getElementById('example5.1');
    var chart = new google.visualization.Timeline(container);
    var dataTable = new google.visualization.DataTable();
    dataTable.addColumn({ type: 'string', id: 'Room' });
    dataTable.addColumn({ type: 'string', id: 'Name' });
    dataTable.addColumn({ type: 'date', id: 'Start' });
    dataTable.addColumn({ type: 'date', id: 'End' });
    dataTable.addRows(newAr);

    var options = {
      timeline: { colorByRowLabel: true }
    };

    chart.draw(dataTable, options);
  }

I don't know why but it is not working

Answer 1

Explanation:

• I believe your goal is to create an array where the third and fourth column are date objects.

• You actually need to apply new Date() to every element in the third and fourth column of your data.

• You can use map() to accomplish your goal:

  const newAr = srcValues.map(r=>[r[0],r[1],new Date(r[2]),new Date(r[3])]);

---

Solution:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName("timeLineData");
  const srcValues = srcSheet.getRange('A2:D'+srcSheet.getLastRow()).getValues();
  const newAr = srcValues.map(r=>[r[0],r[1],new Date(r[2]),new Date(r[3])]);
  Logger.log(newAr)
}

and then you should be able to pass that array to dataTable.addRows:

dataTable.addRows(newAr)