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() {
  varss= SpreadsheetApp.getActiveSpreadsheet();
  varmenuEntries= [ {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() {
  varoldname= SpreadsheetApp.getActiveRange().getNumberFormat();
  varname= Browser.inputBox("Current format (blank = no change):\r\n"+ oldname);
  SpreadsheetApp.getActiveRange().setNumberFormat((name=="")?oldname:name);
}

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


 function sportChrono() {
  varapp= UiApp.createApplication().setTitle('Show Time in mm:ss.SS');
  varmain= app.createGrid(3, 4).setWidth('100');
  varbutton= app.createButton('validate')
  varbtn2= app.createButton('send to Cell').setId('btn2').setVisible(false)
  vartime= app.createTextBox().setId('time').setName('time')
  varcellist= app.createListBox().addItem('A1').addItem('A2').addItem('A3').addItem('A4').addItem('A5').setVisible(false).setId('cellist').setName('cellist')
  varmin= app.createTextBox().setName('min');
  varsec= app.createTextBox().setName('sec');
  varMsec= 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)
  varhandler= app.createServerHandler('show').addCallbackElement(main)
    button.addClickHandler(handler)
  varhandler2= app.createServerHandler('writeToSheet').addCallbackElement(main)
    btn2.addClickHandler(handler2)
  app.add(main)
  ss=SpreadsheetApp.getActive()
  ss.show(app)
}

function show(e){
  var ss=SpreadsheetApp.getActive();
  varapp= UiApp.getActiveApplication();
  varmin= e.parameter.min
  varsec= e.parameter.sec
  varMsec= e.parameter.Msec
  vartime=newDate()
      time.setHours(0,min,sec,Msec)
  varnmin= digit(time.getMinutes())
  varnsec= digit(time.getSeconds())
  varnMsec= digit(time.getMilliseconds())
  app.getElementById('time').setText(nmin+':'+nsec+'.'+nMsec)
  varbtn2= app.getElementById('btn2').setVisible(true)
  varcellist= app.getElementById('cellist').setVisible(true)
  return app
  }

function writeToSheet(e){
  varrange= e.parameter.cellist
  varval= 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}
    elseif(val>9&&val<99){
      str=val.toString()}
    else{
      str=Math.round(val/10).toString()
    }
  return str
}