В этом документе описывается, как определять новые шаблоны форматирования дат и чисел, которые вы можете включать в свой API-запрос.
Форматы даты и времени, а также числовые форматы позволяют управлять тем, как ваши данные отображаются в таблице. Google Sheets предлагает несколько распространенных форматов на выбор, но вы также можете определить свои собственные форматы.
В пользовательском интерфейсе Google Sheets форматирование чисел и дат для ячеек осуществляется с помощью меню «Формат > Число» . В API Google Sheets эти форматы задаются с помощью вызова метода spreadsheets.batchUpdate для отправки запроса UpdateCellsRequest или RepeatCellRequest .
Пример «Установка пользовательского формата даты и времени или десятичного числа для диапазона» демонстрирует, как задать шаблон форматирования с помощью API Google Sheets. Обратите внимание, что фактическое отображение формата зависит от locale электронной таблицы. В этом документе предполагается, что locale — en_US . Вы можете определить locale электронной таблицы, прочитав объект SpreadsheetProperties с помощью метода spreadsheets.get ресурса spreadsheets .
Дополнительные примеры см. в разделах «Базовое форматирование» и «Условное форматирование» .
Информация о значениях даты и времени
Как и большинство других приложений для работы с электронными таблицами, Sheets обрабатывает значения даты и времени как десятичные числа. Это позволяет выполнять над ними арифметические операции в формулах, например, увеличивать количество дней или недель, складывать или вычитать две даты и времени, а также выполнять другие подобные операции.
В Sheets используется формат даты с началом отсчета (epoch date) , широко распространенный в электронных таблицах. Целая часть значения (слева от запятой) отсчитывает количество дней с 30 декабря 1899 года. Дробная часть (справа от запятой) отсчитывает время как долю одного дня. Например, 1 января 1900 года в полдень — это 2.5 , 2 потому что это на 2 дня позже 30 декабря 1899 года, и 0.5 потому что полдень — это половина дня. 1 февраля 1900 года в 15:00 — это 33.625 .
В программе Sheets 1900 год корректно рассматривается как обычный год, а не как високосный.
Шаблоны формата даты и времени
Шаблон формата даты и времени представляет собой строку из подстрок-токенов, которые при разборе заменяются соответствующими элементами даты и времени (например, месяцем или часом).
Токены формата даты и времени
В следующей таблице определены подстроки токенов, которые можно использовать в шаблоне формата даты и времени. Символ + указывает, что предыдущий символ может встречаться один или несколько раз и при этом соответствовать шаблону. Символы, не указанные в этой таблице, рассматриваются как литералы и выводятся без изменений.
| Токен | Описание |
|---|---|
h | Час дня. Переключается между 12- и 24-часовым форматом в зависимости от наличия в строке индикатора AM или PM. |
hh+ | То же самое, что и раньше, но с ведущим 0 для чисел от 1 до 9. |
m | Если предыдущий небуквенный токен был «часы», а последующий — «секунды», то он обозначает минуты в часе (без ведущего нуля). В противном случае он обозначает месяц года в виде числа (без ведущего нуля). |
M | Месяц года без ведущего нуля. Используйте этот символ, чтобы явно указать месяц, а не минуту, в вашем шаблоне. |
mm | То же самое, что и m , но в обоих случаях с ведущим нулем. |
MM | Месяц года с ведущим нулем. Используйте этот символ, чтобы явно указать месяц, а не минуту, в вашем шаблоне. |
mmm | Трехбуквенное сокращение названия месяца (например, "Feb"). |
mmmm | Полное название месяца. mmmmmm+ также соответствует этому. |
mmmmm | Первая буква месяца (например, «J» для июня). |
s | Секунды в минуте без начального нуля. |
ss | Секунды минуты с ведущим нулем. |
[h+] | Количество прошедших часов в заданном временном промежутке. Количество букв указывает минимальное количество цифр (добавляются ведущие нули). |
[m+] | Количество прошедших минут в заданном временном интервале. Количество букв указывает минимальное количество цифр (добавляются ведущие нули). |
[s+] | Количество секунд в заданном временном интервале. Количество букв указывает минимальное количество цифр (добавляются ведущие нули). |
d | День месяца, без ведущего нуля для чисел меньше 10. |
dd | День месяца, с начальной буквой 0 для чисел меньше 10. |
ddd | День недели, трехбуквенное сокращение (например, "Пн"). |
dddd+ | День недели, полное имя. |
y | 2-значный год. |
yy | |
yyy | Четырехзначный год. |
yyyy+ | |
a/p | Отображает "a" для AM и "p" для PM. Также преобразует часы в 12-часовой формат. Если буква-токен заглавная, вывод также будет заглавным. |
am/pm | Как и выше, но вместо "AM" отображается "PM", и все значения всегда заглавные. |
0 | Десятые доли секунды. Вы можете повысить точность до двух знаков после запятой, используя 00 , или до трех знаков после запятой (миллисекунды), используя 000 . |
\ | Следующий символ рассматривается как буквальное значение, а не как имеющий какое-либо особое значение. |
"text" | Отображает текст внутри кавычек как буквальный. |
Примеры форматов даты и времени
Учитывая дату и время Tuesday, April 5, 2016, 4:08:53.528 PM , в следующей таблице приведены некоторые примеры шаблонов и соответствующие им варианты отображения даты и времени. Во втором разделе таблицы показаны примеры форматов прошедшего времени для значения 3 hours, 13 minutes, 41.255 seconds :
| Шаблоны даты и времени | Tuesday, April 5, 2016, 4:08:53.528 PM |
|---|---|
h:mm:ss.00 a/p | 4:08:53.53 p |
hh:mm A/P".M." | 16:08 |
yyyy-mm-dd | 2016-04-05 |
mmmm d \[dddd\] | 5 апреля [вторник] |
h PM, ddd mmm dd | 16:00, вторник, 5 апреля |
dddd, m/d/yy at h:mm | Вторник, 05.04.2016, 16:08 |
| Временные закономерности | 3 hours, 13 minutes, 41.255 seconds |
[hh] | 03 |
[mmmm] | 0193 |
[ss] | 11621 |
Шаблоны формата чисел
Шаблон форматирования числа — это строка из подстрок, которые при разборе заменяются соответствующими числовыми представлениями. Шаблон форматирования числа может состоять из четырех разделов, разделенных точками с запятой, которые определяют отдельные форматы, используемые для положительных чисел, отрицательных чисел, нуля и текста (в указанном порядке):
[POSITIVE FORMAT];[NEGATIVE FORMAT];[ZERO FORMAT];[TEXT FORMAT]
Нет необходимости включать все четыре раздела в формат. Если вы включите только один раздел, этот формат будет использоваться для всех значений. Использование двух разделов приводит к применению первого формата к нулю и положительным числам, а второго — к отрицательным. Использование трех разделов определяет отдельные форматы для положительных, отрицательных и нулевых чисел. Например:
[NUMBER FORMAT] [POSITIVE/ZERO FORMAT];[NEGATIVE FORMAT] [POSITIVE FORMAT];[NEGATIVE FORMAT];[ZERO FORMAT]
Однако, если имеется два или более разделов, и последний раздел представляет собой текстовый формат, то этот раздел рассматривается как текстовый формат, а остальные ведут себя так, как если бы разделов стало на один меньше. Таким образом, включение последнего текстового формата позволяет определять такие форматы, как:
[POSITIVE/ZERO FORMAT];[NEGATIVE FORMAT];[TEXT FORMAT] [NUMBER FORMAT];[TEXT FORMAT]
Разделение формата на разделы происходит до остальных операций, поэтому можно включить формат даты или времени в один из разделов (хотя это имеет ограниченную полезность).
Токены числового формата
В приведенной ниже таблице определены подстроки токенов, которые можно использовать в разделе форматирования для определения способа представления значений в этом разделе.
| Токен | Описание |
|---|---|
0 | Представляет собой цифру в числе. Если цифра несущественная, например, ноль, она отображается как 0. Например, в формате 00.0 число 3 отображается как " 03.0 ". |
# | Представляет собой цифру в числе. Если цифра несущественная (0), она не отображается. Например, числовой формат ##0 отображает число 12 как " 12 ". |
? | Обозначает цифру в числе. Если цифра несущественная (0), она отображается как пробел. Это часто используется для выравнивания десятичной точки в столбце при использовании шрифтов фиксированной ширины. Например, формат числа ???.??? отображает число 12.4 как: " 12.4 ". |
. | Первая точка обозначает десятичную запятую в числе. Последующие точки отображаются как литералы. Если вы включите десятичную запятую в формат, она всегда будет отображаться, даже для целых чисел. Например, #0.# отображает число 3 как " 3. " . |
% | Этот символ отображается как литерал, но также приводит к умножению существующих чисел на 100 перед отображением, чтобы сделать проценты более читабельными. Например, числовой формат #% отображает число 0,25 как " 25% ". |
, | Если символ находится между двумя цифрами ( 0 , # или ? ), он отображает все число с разделителями групп (группировка по тысячам). Если он следует за цифрами, то цифры масштабируются на тысячу за каждую запятую (например, формат #0.0,, отображает число 12 200 000 как 12.2 ). |
E- | Выводит число в научном формате, при этом форматирование слева от буквы E используется для неэкспоненциальной части, а форматирование справа от буквы E — для экспоненциальной части. E+ обозначает знак + для положительных показателей степени. E- обозначает знак только для отрицательных показателей степени. Если используется строчная буква, вывод e также будет в нижнем регистре. Например, числовой формат ##0.00#E+## преобразует число 0.0000001 в " 100.00E-9 ". |
E+ | |
e- | |
e+ | |
/ | Если символ / находится между двузначными символами ( 0 , # или ? ), то эти группы цифр обрабатываются как дробный формат. Например, числовой формат 0 #/# отображает число 23,25 как 23 1/4 . Знаменатель также может быть целым числом, в этом случае в качестве знаменателя используется именно это целое число. Числовой формат 0 #/8 отображает число 23,25 как 23 2/8 . Дробная часть не отображается, если числитель равен 0. Число 23,1 с числовым форматом 0 #/3 отображается просто как 23 (поскольку 0,1 округляется до 0/3). Символ / несовместим с научным форматом или форматом с десятичной точкой. |
* | Это добавлено для совместимости с числовыми форматами Microsoft Excel. Этот параметр игнорируется. |
_ | Символ подчеркивания пропускает следующий символ и заменяет его пробелом. Это используется для выравнивания числовых форматов, где отрицательное значение заключено в скобки. |
\ | Следующий символ рассматривается как буквальное значение, а не как какое-либо особое значение, которое он может иметь. Например, числовой формат \#0 преобразует число 10 в " #10 ". |
"text" | Отображает текст внутри кавычек как буквальный. |
@ | Если в ячейке есть текстовое поле ввода, вставляется исходный текст из этой ячейки. Несовместимо с другими специальными символами и не отображается для числовых значений (которые отображаются в общем формате). |
$ - + ( ) : space | В программе Sheets эти символы рассматриваются как буквальные и отображаются без изменений. |
Мета-инструкции
Кроме того, каждый из разделов форматирования может содержать необязательные мета-инструкции, заключенные в квадратные скобки [] , которые предшествуют формату и предоставляют дополнительные указания. Существует два типа мета-инструкций, и данный раздел может использовать оба:
| Инструкция | Описание |
|---|---|
[ condition ] | Заменяет стандартное положительное, отрицательное или нулевое сравнение раздела другим условным выражением. Например, [<100]"Low";[>1000]"High";000 отображает слово "Низкий" для значений ниже 100, "Высокий" для значений выше 1000 и трехзначное число (с ведущими нулями) для любых значений между ними. Условия могут применяться только к первым двум подформатам, и если число соответствует более чем одному формату, используется первый из них. Если есть третий формат, он используется для "всего остального", в противном случае, если число не соответствует ни одному из форматов, оно отображается в виде символов "#", заполняющих ширину ячейки. Если существует четвертый формат, для текста всегда используется четвертый формат. |
[ Color ] or [ Color# ] | Этот параметр заставляет любое значение, отображаемое в подформате, выглядеть с заданным цветом текста. Допустимые значения для Color : черный, синий, голубой, зеленый, пурпурный, красный, белый или желтый. Допустимые значения для символа "#" в Color# — от 1 до 56. Цвета числового формата переопределяют любые цвета, введенные пользователем в ячейку, но не переопределяют цвета, заданные условным форматированием. Эта цветовая палитра отображает список цветов, соответствующих каждому числу. Они основаны на свойстве ColorIndex в Microsoft Excel. |
Примеры форматов чисел
В следующей таблице приведены примеры шаблонов и соответствующие им варианты отображения чисел в отформатированном виде:
| Число | Шаблон | Отформатированное значение |
|---|---|---|
| 12345.125 | ####.# | 12345.1 |
| 12.34 | 000.0000 | 012.3400 |
| 12 | #.0# | 12.0 |
| 5.125 | # ???/??? | 5 1/8 |
| 12000 | #,### | 12 000 |
| 1230000 | 0.0,,"M" | 1,2 млн. |
| 1234500000 | 0.00e+00 | 1.23e+09 |
| 123114.15115 МойТекс | ###0.000;"TEXT: "_(@_) | 123114.151 ТЕКСТ: МойТекст |
| 1234 -1234 0 МойТекс | [Blue]#,##0;[Red]#,##0;[Green]0.0;[Magenta]_(@_) | 1,234 1,234 0.0 МойТекс |
| 1005 32 527 | [>1000]"HIGH";[Color43][<=200]"LOW";0000 | ВЫСОКИЙ НИЗКИЙ 0527 |