apps-script：使用格式将电子表格单元格复制到表格 [英] apps-script: copying a spreadsheet cell to a table, with formatting

问题描述

我正在编写一个脚本，通过URL打开外部Google电子表格。该脚本需要将单元格复制到FlexTable中，并将其显示出来。

问题在于电子表格单元格具有自定义显示格式，以便将其显示为已用时间（m ：SS.SS）。如果我只是用 table.setWidget（x，y，app.createLabel（z））加载表格元素，那么标签只会在1899年显示一个日期。我可以复制格式？

谢谢。

编辑

这几乎是这样：

  date = new Date（z）; 'getValues'
已过期= Utilities.formatDate（日期，GMT，m：ss.SS）; 
 table.setWidget（x，y，app.createLabel（elapsed））; 
  

不幸的是，m：ss.SS不起作用;它总是显示毫秒为0.任何想法？

另一个编辑

应用程序脚本似乎已完全搞砸了。此代码：

  date = new Date（）; 
逝去= Utilities.formatDate（日期，GMT，m：ss.SS）; 
 table.setWidget（x，y，app.createLabel（elapsed））; 
  

正确显示当前时间的分钟和秒钟部分，但 3 <

  date = new Date（z）; //'z'from'getValues'
  

不起作用。当电子表格中显示 z 时，该构造函数始终将ms数设置为0（ getMilliseconds 返回0）。
任何人都有解决方法？我需要应用程序脚本来处理田径事件时间，通常是秒到2位小数，可能几分钟。
我注意到这些对应于1899年12月30日的日期。这很奇怪 - 这个日期不应该有负值吗？ formatDate 的GWT源代码将负值作为特殊情况处理，并且很难看到它在做什么。

解决方案

这个区域有多个相关的和未定义的错误;例如，请参阅此处。让Google表单处理经过的时间是一个糟糕的主意。我最终的解决方案是使用整数来保存时间，并且在GAS中进行所有必要的格式化和处理（仔细地说，因为它们实际上是不精确的浮点数）。这很容易，而且比 Date 更好。唯一的麻烦是如果您需要从Excel导入日期。我必须修改Excel电子表格以将日期转换为ms，然后导入它们。

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.

解决方案

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.

这篇关于apps-script：使用格式将电子表格单元格复制到表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！