28.02.2018 перестала работать функция “Вставить результат вычисления формулы как значения”

#google_spreadsheet #google_apps_script

                    
Именно сегодня перестала работать функция - вставить результат вычисления формулы
как значения. (В Google Apps Scripts, Google Spreadsheets). На сейчас эта функция работает
как clearContents() - просто затирает все значения.

Пример:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet()
  var range = sheet.getRange("A1:C3")
  range.setFormula("=1+2")

  range.copyTo(range, {contentsOnly:true})
  //Или так:
  //range.copyValuesToRange(sheet, range.getColumn(), range.getLastColumn(), range.getRow(),
range.getRowIndex())
  //range.copyTo(range, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
}


Можно использовать другие методы, поколдовав с форматами, но почему же перестало
работать??

range.setValue(range.getValues())
range.setValue(range.getDisplayValues())

    

Ответы

Ответ 1

Вдруг кому интересно будет не смотря на минусовой вопрос:

Необходимо добавить перед range.copyTo(range, {contentsOnly:true})  SpreadsheetApp.flush()

Согласно документации, этот метод применяет все ожидающие изменения в таблице 


  Applies all pending Spreadsheet changes.


То есть, как я понимаю, ранее, при вызове copyTo, google перед тем, как брать данные
из ячеек, сам применял все изменения (высчитывал результаты формул), а после этого
уже делал копирование. Сейчас же нужно этот момент задавать вручную.

UPD1:
Согласно комментарию oshliaer, можно и Sheets API использовать.

Насколько я понимаю, код будет где-то такой:

// ss = SpreadsheetApp.getActiveSpreadsheet()
// sheet = ss.getSheetByName("NAME")
//Для примера: range = sheet.getRange("A1:C")
function killAllFormulas2(ss, sheet, range) {
  // Обязателен вызов flush(), иначе если вы только вставили сложные формулы и сразу
захотели вставить результаты вычисления как значения - они могут не успеть обновить
данные в ячейках
  SpreadsheetApp.flush()
  // Получаем ID вашего Spreadsheet
  var ssID = ss.getId()
  // Если выбран такой диапазон как в примере - его обязательно нужно перевести в
вид A1:C1000 (условно)
  var rangeA1 = sheet.getRange(range.getRow(), range.getColumn(), range.getLastRow()-range.getRow()+1,
range.getLastColumn()-range.getColumn()+1).getA1Notation()
  // Получаем значения  
  var values = Sheets.Spreadsheets.Values.get(ssID, sheet.getName() + "!" + rangeA1)
  // Вставляем значения
  Sheets.Spreadsheets.Values.update(values, ssID, sheet.getName() + "!" + rangeA1,
{valueInputOption:"USER_ENTERED"})
 }


В примерах, которые встречаются вот здесь указано, что для большинства случаев использования
лучше использовать стандартные методы встроенного SpreadsheetApp (например цитата для
записи данных: 


  This code uses the Sheets Advanced Service, but for most use cases the built-in
method SpreadsheetApp.getActiveSpreadsheet().getRange(range).getValues(values) is more
appropriate.


По скорости работы я проверял на некоторых диапазонах со сложными формулами - разницы
вообще не увидел. Скорее всего это все один и тот же алгоритм.

Надеюсь, этот ответ поможет кому-то не тратить драгоценное время на поиски истины.

? вместо эмодзи

#javascript #google_apps_script

Гугл скрипт связан вебхуком с телеграм ботом. Я хочу, чтобы сообщение из бота разбивалось
на нужные мне куски и отправлялось в гугл таблицу.
Проблема возникает с извлечением эмодзи

  var contents = JSON.parse(e.postData.contents);
  var text = contents.message.text
  var textar = text.split("");
  var sheet = SpreadsheetApp.openById(ssId);
  sheet.getSheetByName(sheetName).appendRow([text,textar[4]]);


Таким образом мне приходит оригинальный текст + конкретный символ(на котором обязательно
будет эмодзи). Однако в таблице появляется "?", хотя в соседней ячейке эмодзи прекрасно
отображается в рамках всего сообщения.
Как изменить код, чтобы скрипт верно отправлял на запись именно эмодзи и не терял его?
    

Ответы

Ответ 1

text.split(""); правильно разбивает только те строки, которые состоят из юникод-символов,
закодированных двумя байтами в кодировке UTF-16 (code unit). Эмодзи, как правило, кодируются
четырмя байтами (2 code units), поэтому разбиваются на две части так называемой суррогатной
пары, каждая из которых не может быть самостоятельным символом и представляется знаком
вопроса при отображении. Правильная разбивка может быть сделана при помощи [...text]
выражения, так как в современном JavaScript строка поддерживает протокол итерации,
который разбивает её на символы, независимые от кодировки (code points). Поэтому попробуйте:

var textar = [...text];


Подробнее можно почитать:

The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About
Unicode and Character Sets (No Excuses!) – Joel on Software

What every JavaScript developer should know about Unicode

JavaScript has a Unicode problem · Mathias Bynens

Unicode-aware regular expressions in ES2015 · Mathias Bynens

ES6 Strings (and Unicode, ❤) in Depth

Jonathan New | "💩".length === 2

P.S. Если вы ограничены использованием ES5, можно попробовать комбинацию из полифилов
String.prototype.codePointAt() + String.fromCodePoint() по сылкам (т.е. text.split("")[4]
заменить на String.fromCodePoint(text.codePointAt(4))).

Скрипт в Google Spreadsheets. Как отследить событие изменения в ячейке?

#javascript #события #google_spreadsheet #google_apps_script

Всем добрый день. Составляем файлик, где будем вести семейный бюджет. Столкнулся
со следующей задачей. Есть "направление" и "категория продуктов". В одном направлении
собраны определенные категории продуктов. Также есть форма ввода (типа опроса), что
бы мы могли забивать транзакции через мобильники. Так вот, нужно чтобы скрипт анализировал
категорию продуктов и автоматом проставлял направление. Допустим категория "метро",
автоматом проставляется в соседнем столбце направление "транспорт". Я написал пример
скрипта, который читает содержимое текущей ячейки и проставляет результаты в первой.
Вопрос в том, как это все повесить на событие ввода (нажал enter - скрипт отработал)
function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
//  var first = Browser.inputBox("First value");
  if (SpreadsheetApp.getActiveRange().getValue() == "пиво"){
    sheet.getRange("A1").setValue(SpreadsheetApp.getActiveRange().getValue());}
//  sheet.getRange("A1").setValue("First value:");
//  sheet.getRange("B1").setValue(first);
//  var next = Browser.inputBox("Next value"); 
//  sheet.getRange("A2").setValue("Next value:");
//  sheet.getRange("B2").setValue(next);
//  var result = sheet.getRange("B1").getValue() + sheet.getRange("B2").getValue();
//  sheet.getRange("A3").setValue("Result:");
//  sheet.getRange("B3").setValue(result);
//  Browser.msgBox("Summ is: " + result);
  ss.addMenu("Test", [{name: "Test", functionName: "myFunction"}]);
}
    

Ответы

Ответ 1

Вероятно, использовать триггер onEdit(event):

onEdit(event)
The onEdit function runs automatically when any cell of the spreadsheet is edited.
A very simple use case for onEdit is to record the last modified time in a comment
on the cell that was edited. The argument e that is passed in to the function contains
a single property, source , which is the spreadsheet that is being edited.
function onEdit(event)
{
  var ss = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  r.setComment("Last modified: " + (new Date()));
}


Ответ 2

Все очень просто, используйте функцию onEdit(event) которая реагирует только на изменение
данных в таблице.
Пример готового решения который при изменение любой строки вносит данные в столбец
номер 8, номера строки та которая была изменена Вами 

function onEdit(event) {


  var sheet = event.source.getActiveSheet();
  var sheetName = event.source.getActiveSheet().getSheetName() // Получаем имя листа
который активен 
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();


  if (index > 1 && sheetName == "Менеджер") {
  Logger.log(event.source.parameters);
  //var user = Session.getEffectiveUser().getEmail();
  var user = Session.getActiveUser().getEmail();
  Logger.log(index);
  sheet.getRange(index, 8).setValue(user);
}


Исходя из Вашей задачи Вам осталось только переписать условие iF.

Обмен данными между таблицами Google Spreadsheet

Как реализовать обмен данными между таблицами Google Spreadsheet?
Для наглядности визуализировал все на картинке:
Таблицы находятся в разных документах.
При редактировании ячеек информация должна обновиться в другой таблице. Соответствие строк должно соблюдаться.

Ответ

Для этого не нужен Apps Script, задача решается встроенными функциями importrange и query. А именно, importrange включает часть одной таблицы в другую, например
=importrange("...", "Sheet5!I1:M10")
где в ... надо поместить ссылку на другую таблицу, взяв её из адресной строки браузера. При первом использовании понадобится подтвердить разрешение на импорт данных.
Затем, выбрать только нужные данные посредством query (синтакс подобен SQL):
=query(importrange("...", "Sheet5!I1:M10"); "select Col3,Col4,Col5,Col6,Col7 where Col8 = 'В таблицу 1'")
Отмечу, что Col3, например, означает третий столбец импортированного куска таблицы. В этом примере выбраны только строки, где в 8-м столбце указано В таблицу 1