apps-script: copying a spreadsheet cell to a table

问题:

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 cells have a custom display format, to show them as elapsed times (m:ss.SS). If I just load up the table element with table.setWidget(x, y, app.createLabel(z)), the label just displays a date in 1899. Any idea how I can copy over the formatting?

Thanks.

EDIT

This nearly does it:

date = new Date(z);  // 'z' from 'getValues'
elapsed = Utilities.formatDate(date, "GMT", "m:ss.SS");
table.setWidget(x, y, app.createLabel(elapsed));

Unfortunately, "m:ss.SS"doesn't work; it always displays the milliseconds as 0. Any ideas?

ANOTHER EDIT

apps-script seems to have completely messed this up. This code:

date = new Date();
elapsed = Utilities.formatDate(date, "GMT", "m:ss.SS");
table.setWidget(x, y, app.createLabel(elapsed));

Correctly shows the minutes and seconds portion of the current time, but there are 3 decimal places shown, not 2. This code:

date = new Date(z);  // 'z' from 'getValues'

Doesn't work. When z is displayed in the spreadsheet with a non-zero number of milliseconds, this constructor always sets the number of ms to 0 (getMilliseconds returns 0). Anyone have a work-around? I need apps-script to handle athletics event times, which are generally seconds to 2 decimal places, and possibly a few minutes. I've noticed that these correspond to dates on December 30, 1899. This is odd - shouldn't this date have a negative time value? The GWT source code for formatDate handles negative values as a special case, and it's difficult to see what it's doing.

回答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.

回答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
}