Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-visualization

Unable to fetch 'datetime' in correct format from Google sheet for Google chart on Google web apps

I added Google line chart (from here) on HTML page on Google web app and the data table comes from Google Sheets. Initially, line chart was accepting 'number' for x-axis but I changed it to 'datetime' and it accept date in "new Date(yyyy, MM, dd, HH, mm,ss)" But i am able to change the format of the date but chart is not working.

My problem is similar to this question, but this is my first time working with Google web app or JavaScript. So, i was unable to understand the solution.

Below is the sample data table:

Date SP1 Sp2
02/09/2021 00:02:34 2 3.5
02/09/2021 01:02:34 4 7
02/09/2021 02:02:34 10 14

Code.gs:

function getTableDataQQ() {
  const ws = SpreadsheetApp.openById('id').getSheetByName('August');
  const data = ws.getRange(2, 1, ws.getLastRow() - 1, 3).getValues();
  const sheetname = ws.getSheetName();

  return data.map((r) => [
    Utilities.formatDate(
      new Date(r[0]),
      'GMT+0530',
      'yyyy, MM, dd, HH, mm, ss'
    ),
    parseInt(r[1]),
    parseInt(r[2]),
  ]);
}

HTML:

<script type="text/javascript">
    google.charts.load('current', {
        'packages': ['line']
    });
    google.charts.setOnLoadCallback(getData);

    function getData() {
        google.script.run.withSuccessHandler(drawChart).getTableDataQQ();
    }

    function drawChart(dataReturned) {

        var data = new google.visualization.DataTable();
        data.addColumn('number', 'Days');
        data.addColumn('number', 'SP1');
        data.addColumn('number', 'SP2');
        data.addColumn('number', 'SP3');


        data.addRows(dataReturned);


        var opt = {

            title: 'Text 1',
            subtitle: 'Text 2'

        };
        var chart = new google.charts.Line(document.getElementById('chart'));

        chart.draw(data, google.charts.Line.convertOptions(opt));
    }
</script>

<body>
    <div id="chart" style="width: 1000px; height: 400px"></div>
</body>

I made changes in the original line chart script using this YT tutorial. If add required data in the original script (like below) then the Line chart works, but I don't how to connect the original code with the Google sheet cause I follow the YT tutorial

Original Scrpt:

<script type="text/javascript">
    google.charts.load('current', {
        'packages': ['line']
    });
    google.charts.setOnLoadCallback(drawChart);

    function drawChart() {

        var data = new google.visualization.DataTable();
        data.addColumn('datetime', 'Day');
        data.addColumn('number', 'Guardians of the Galaxy');
        data.addColumn('number', 'The Avengers');
        data.addColumn('number', 'Transformers: Age of Extinction');

        data.addRows([
            [new Date(2021, 09, 01, 19, 02), 37.8, 80.8, 41.8],
            [new Date(2021, 09, 01, 19, 32), 30.9, 69.5, 32.4],
            [new Date(2021, 09, 01, 20, 02), 25.4, 57, 25.7],
            [new Date(2021, 02, 01, 20, 02, 00), 4.2, 6.2, 3.4]
        ]);

        var options = {
            chart: {
                title: 'Box Office Earnings in First Two Weeks of Opening',
                subtitle: 'in millions of dollars (USD)'
            },
            width: 900,
            height: 500
        };

        var chart = new google.charts.Line(document.getElementById('chart'));

        chart.draw(data, google.charts.Line.convertOptions(options));
    }
</script>

Thanks

Solution

  • I believe your current issue as follows.

    • In your script, the value of column "A" is like '2021,02,08,21,32,34'. In this case, this cannot be directly used as the date scale.
    • In your sample Spreadsheet, the data has 3 columns. But in your Javascript, 4 columns are used.
    • About data.addColumn('number', 'Days');, from your sample Date values, 02/09/2021 00:02:34, 02/09/2021 01:02:34 and 02/09/2021 02:02:34, in this case, I thought that timeofday might be suitable.

    When above issue was resolved from your script, it becomes as follows.

    Modified script:

    In this case, please modify your Javascript as follows.

    From:

    var data = new google.visualization.DataTable();
data.addColumn('number', 'Days');
data.addColumn('number', 'SP1');
data.addColumn('number', 'SP2');
data.addColumn('number', 'SP3');


data.addRows(dataReturned);

    To:

    var data = new google.visualization.DataTable();
data.addColumn('timeofday', 'Days');
data.addColumn('number', 'SP1');
data.addColumn('number', 'SP2');
var obj = dataReturned.map(([a, ...b]) => [a.split(",").slice(-3).map(e => Number(e)), ...b]);
data.addRows(obj);

    Of, when you want to use datetime, you can also use the following modification.

    To:

    var data = new google.visualization.DataTable();
data.addColumn('datetime', 'Days');
data.addColumn('number', 'SP1');
data.addColumn('number', 'SP2');
var obj = dataReturned.map(([a, ...b]) => [new Date(...a.split(",").map((e, i) => i == 1 ? Number(e) - 1 : e)), ...b]);
data.addRows(obj);

    Reference: