Google app scripts: How to set date format on horizontal axis chart on Spreadsheet?

Question

I'm trying to create a chart from data on spreadsheet, but I have troubles managing the format on horizontal axis on the new created chart.

I've tried to utilice the method "setOption('vAxis.format', 'dd/MM/YY')" but it doesn't work, showing the ticks labels like "dd/MM/YY 00:00:00": Figure 1

If I change the date format on the spreadsheet so i get a deformed values on second chart: Figure 2

The spreadsheet: Link

Google script code:

function Test_format_chart() {
var ss=SpreadsheetApp.openById("1lVqL5nT1IS3T2LPrcB5QAg_03ni0JgoJgxbAWdWb0I")
var sh=ss.getSheetByName("TestSheet");
var chts=sh.getCharts();

 for(var i=0;i<chts.length;i++) {
   sh.removeChart(chts[i]);
 }
var chartBuilder = sh.newChart();
chartBuilder.addRange(sh.getRange("A1:B25"))
.setChartType(Charts.ChartType.LINE)
.setOption('useFirstColumnAsDomain', true) 
.setPosition(2, 4, 0, 0)
.setOption('title', 'My Line Chart!')
.setOption('hAxis.textStyle',{color:'#ff0000'})
.setOption('hAxis.format', 'dd/MM/YY')
sh.insertChart(chartBuilder.build());  
}

Keep in mind that the previous code is only a simplicated view of original implementation. In the original project we export the second chart in an e-mail, but the problem that we see is the same. Thanks!

--

27/09/2019 update:

I'm trying a new strategy, using unixtime format date on the column, and later try to replace the value on horizontal axis with a literal string as the line chart documentatión shows on the 'hAxis.ticks' option. But I'm not obtaining the expected results, the horizontal ticks labels doesn't change with the setOption('hAxis.ticks',....) variations I'm traying.

Some code:

function Test_format_chart() {
var ss=SpreadsheetApp.openById("1lVqL5nT1IS3T2LPrcB5QAg_03ni0JgoJgxbAWdWb0I")     
var sh=ss.getSheetByName("TestSheet");
var chts=sh.getCharts();

 for(var i=0;i<chts.length;i++) {
   sh.removeChart(chts[i]);
 }

 var chartBuilder = sh.newChart();
 chartBuilder.addRange(sh.getRange("A1:B25"))
  .setChartType(Charts.ChartType.LINE)
  .setOption('useFirstColumnAsDomain', true) 
  .setPosition(2, 4, 0, 0)
  .setOption('title', 'My Line Chart!')
  .setOption('hAxis.textStyle',{color:'#ff0000'})
  .setOption('hAxis.ticks', [1568023200, 156802324900])// <--

  sh.insertChart(chartBuilder.build());  
}

Answer 1

This is a work around. I couldn't find any other way to do it than to just change the numberFormat for the date column in your sheet.

function Test_format_chart() {
  var ss=SpreadsheetApp.openById("1lVqL5nT1IS3T2LPrcB5QAg_03ni0JgoJgxbAWdWb0I")
  var sh=ss.getSheetByName("TestSheet");
  var rg=sh.getRange(2,1,sh.getLastRow()-1,1).setNumberFormat("dd/mm/yy");//Modify the format for column one date data.
  var chart=sh.getCharts()[0].modify()
  .setPosition(5,10,0,0)
  .setOption('title', 'Formated new chart')
  .build();
  sh.updateChart(chart); 
}

Class Embedded Chart

This is the script I've been using to do my testing for last couple of hours:

function createChart() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var rg= sh.getRange(1,1,sh.getLastRow()-1,sh.getLastColumn());
  var drg=sh.getRange(2,1,sh.getLastRow()-1,1).setNumberFormat("dd/mm/yy");
  var chts=sh.getCharts();
  for(var i=0;i<chts.length;i++) {
    sh.removeChart(chts[i]);
  }
  var chartBuilder = sh.newChart();
  chartBuilder.addRange(rg)
  .setChartType(Charts.ChartType.LINE)
  .setPosition(2, 4, 0, 0)
  .setOption('title', 'My Line Chart!')
  .setOption('hAxis.textStyle',{color:'#ff0000'});
  sh.insertChart(chartBuilder.build());
}

This is what my Sheet1 looks like:

Notice that it changes the formatting of the date column

This is probably not what you wanted. Sorry.