Charts.newDataTable() not working with Dates
From within a Google Ads script, I'm trying to create a basic line chart as described here: https://developers.google.com/apps-script/reference/charts/line-chart-builder
The data that I'm looking to chart is Date X Clicks.
I'm able to generate the chart with my data from Google Ads, however, Dates are being formatted as Strings and not actual dates within the chart which is causing other issues.
For example, this code will work with my data but formats the dates as strings within the chart:
var dataBuilder = Charts.newDataTable(); dataBuilder.addColumn(Charts.ColumnType.STRING, 'Date'); dataBuilder.addColumn(Charts.ColumnType.NUMBER, 'Clicks'); When I try this code, it does not work:
var dataBuilder = Charts.newDataTable(); dataBuilder.addColumn(Charts.ColumnType.DATE, 'Date'); dataBuilder.addColumn(Charts.ColumnType.NUMBER, 'Clicks'); I have tried to convert the date data from this: ["2019-09-27",75],["2019-09-29",102],["2019-9-30",112]
To something like this: ["new Date(2019,9,27)",75],["new Date(2019,9,29)",102],["new Date(2019,9,30)",112]
Based on the documentation that I found here: https://developers.google.com/chart/interactive/docs/datesandtimes
But that does not seem to work.
Any suggestions?
In the end, I was able to solve this problem by exporting the Google Ads data to a Google Sheet and then reading that sheet back into Google Ads for the chart.
I did something like this which worked for me:
function BuildChart(myMetric, ssName) { //Load the spreadsheeet var sheet = ss.getSheetByName(ssName); //Grab all the data in the sheet var data = sheet.getRange('A1:B29').getValues(); //Start Building the data table var dataTable = Charts.newDataTable(); dataTable.addColumn(Charts.ColumnType.DATE, data[0][0]); dataTable.addColumn(Charts.ColumnType.NUMBER, data[0][1]); //Add rows for(var j=1; j<data.length; j++){ Logger.log(data[0][1] + ':' + data[j]) dataTable.addRow([data[j][0],data[j][1]]); } //Create and build chart var chartBuilder = Charts.newLineChart() .setDataTable(dataTable) .setTitle(myMetric + ' over the past 28 days') .setXAxisTitle('Date') .setYAxisTitle(myMetric) .setDimensions(850, 350) .setOption('legend.position', 'none') .setOption('trendlines', {'type':'linear','color':'purple'}) .setCurveStyle(Charts.CurveStyle.NORMAL) .setPointStyle(Charts.PointStyle.MEDIUM); var chart = chartBuilder.build(); return chart; }