I am using the google visualisation API to query a spreadsheet that has dates in one column, the problem is that when I use the dates returned from the query to populate another spreadsheet, they are incorrectly formatted.
Thus far I have already tried the following:
var queryResult = sheetQuery(
"Sheet1",
"A1:A",
"select A format A 'dd/mm/yyy'"
);
var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadSheet.getSheetByName("Sheet1");
var range = sheet.getRange(1, 2, 8, 1);
range.clear();
range.setValues(queryResult).setHorizontalAlignment("left");
please pay particular attention to my use of the format clause at the end of the query (4th parameter).
I have also tried to manually format the date cells as follows:
sheet.getRange(1, 2, 8, 1).setNumberFormat("dd/mm/yyyy");
However, the dates simply do not format and stay formatted like this: Date(2018,8,21,9,0,0) Date(2018,8,21,9,15,4) Date(2018,8,21,9,30,0) Date(2018,8,21,9,45,0) Date(2018,8,23,10,0,19) Date(2018,8,24,10,15,0) Date(2018,8,24,10,30,0) Date(2018,8,24,10,45,43)
I have set up an example spreadsheet here: https://docs.google.com/spreadsheets/d/160sp6T0dEvq_s5X6gGx_mH-9dj2fBf7co80On01JqWw/edit?usp=sharing
To reproduce the issue, find mainFunction script and run. here is a link to the script: https://script.google.com/d/1kRavDwa0HKSuFRk6UKram1j7UzcaSow7mwfWBolSZoHwARaVF6Ifh_iF/edit?usp=sharing
Any help would be greatly appreciated!