Apps-script: Copying A Spreadsheet Cell To A Table, With Formatting

I'm writing a script which opens an external Google spreadsheet via a URL. The script needs to copy cells into a FlexTable, and display them. The problem is that the spreadsheet ce

Solution 1:

There are multiple related and unfixed bugs in this area; see here, for example. It's a bad, bad, idea to let Google sheets handle elapsed times. My eventual solution was to use "integers" to hold the times, and do all the required formatting and processing in GAS (carefully, because they're actually inexact floats, of course). This is pretty easy, and is much better than battling with Date. The only complication is if you need to import Dates from Excel. I had to modify the Excel spreadsheet to convert the dates to ms, and import those instead.

Solution 2:

here is a possible workaround to show time the way you want : testsheet (please don't modify)

I used an Ui to define the time value but converted it to a date object in the script to verify how it works with a "real" date object.

EDIT : following the different answers to this post and this other by AdamL, I finally get something that allows displaying and calculating with short time values in hundreds of seconds with help of a UI and custom formatting.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Change cell format", functionName: "EditFormat"},
                      {name: "Reset format to default", functionName: "ResetFormat"},
                      {name: "show UI test", functionName: "sportChrono"},
                    ]               
  ss.addMenu("Custom Format", menuEntries);
}

function EditFormat() {
  var oldname = SpreadsheetApp.getActiveRange().getNumberFormat();
  var name = Browser.inputBox("Current format (blank = no change):\r\n"+ oldname);
  SpreadsheetApp.getActiveRange().setNumberFormat((name=="")?oldname:name);
}

function ResetFormat() {
  SpreadsheetApp.getActiveRange().setNumberFormat("0.###############");
}


 function sportChrono() {
  var app = UiApp.createApplication().setTitle('Show Time in mm:ss.SS');
  var main = app.createGrid(3, 4).setWidth('100');
  var button = app.createButton('validate')
  var btn2 = app.createButton('send to Cell').setId('btn2').setVisible(false)
  var time = app.createTextBox().setId('time').setName('time')
  var cellist = app.createListBox().addItem('A1').addItem('A2').addItem('A3').addItem('A4').addItem('A5').setVisible(false).setId('cellist').setName('cellist')
  var min = app.createTextBox().setName('min');
  var sec = app.createTextBox().setName('sec');
  var Msec = app.createTextBox().setName('Msec');
    main.setText(0,0,'minutes').setText(0,1,'secs').setText(0,2,'millisecs')
    main.setWidget(1,0,min).setWidget(1,1,sec).setWidget(1,2,Msec);
    main.setWidget(1,3,button).setWidget(2,0,time).setWidget(2,1,cellist).setWidget(2,2,btn2)
  var handler = app.createServerHandler('show').addCallbackElement(main)
    button.addClickHandler(handler)
  var handler2 = app.createServerHandler('writeToSheet').addCallbackElement(main)
    btn2.addClickHandler(handler2)
  app.add(main)
  ss=SpreadsheetApp.getActive()
  ss.show(app)
}

function show(e){
  var ss=SpreadsheetApp.getActive();
  var app = UiApp.getActiveApplication();
  var min = e.parameter.min
  var sec = e.parameter.sec
  var Msec = e.parameter.Msec
  var time = new Date()
      time.setHours(0,min,sec,Msec)
  var nmin = digit(time.getMinutes())
  var nsec = digit(time.getSeconds())
  var nMsec = digit(time.getMilliseconds())
  app.getElementById('time').setText(nmin+':'+nsec+'.'+nMsec)
  var btn2 = app.getElementById('btn2').setVisible(true)
  var cellist = app.getElementById('cellist').setVisible(true)
  return app
  }

function writeToSheet(e){
  var range = e.parameter.cellist
  var val = e.parameter.time
  var ss=SpreadsheetApp.getActive();
    ss.getRange(range).setFormula('=value("0:'+val+'")');
  }

function digit(val){
  var str
    if(val<10){
      str='0'+val}
    else if(val>9&&val<99){
      str=val.toString()}
    else{
      str=Math.round(val/10).toString()
    }
  return str
}