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.


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

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

Скрипт в 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.

Отправка формы при помощи JS?

#javascript #sendmail #google_spreadsheet

Добрый день, хочу поинтересоваться, кто-то делал отправку писем через гугл таблицы?
Т.е, проворачивал ли, кто-то вот такую схему "сайт -> гугл таблица -> почта" при помощи
js? Или же, можете ли вы, подсказать иной способ отправки форм с сайта, с использованием js?
    

Ответы

Ответ 1

Или же, можете ли вы, подсказать иной способ отправки форм с сайта, с
  использованием js?


Да, такие варианты есть, например - использовать EmailJS. Другой вариант - использовать
JS GMail API. Остальные варианты без труда ищутся по запросу send emails javascript.


  Добрый день, хочу поинтересоваться, кто-то делал отправку писем через
  гугл таблицы? Т.е, проворачивал ли, кто-то вот такую схему "сайт ->
  гугл таблица -> почта" при помощи js?


Именно такая схема не имеет смысла, есть смысл просто параллельно организовать в
коде отправку и письма, и лида в GD. Для работы с последним вы также можете найти множество
примеров реализации в сети, включая документацию от самого Google.

Обмен данными между таблицами 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

Отправка формы при помощи JS?

Добрый день, хочу поинтересоваться, кто-то делал отправку писем через гугл таблицы? Т.е, проворачивал ли, кто-то вот такую схему "сайт -> гугл таблица -> почта" при помощи js? Или же, можете ли вы, подсказать иной способ отправки форм с сайта, с использованием js?

Ответ

Или же, можете ли вы, подсказать иной способ отправки форм с сайта, с использованием js?
Да, такие варианты есть, например - использовать EmailJS. Другой вариант - использовать JS GMail API. Остальные варианты без труда ищутся по запросу send emails javascript
Добрый день, хочу поинтересоваться, кто-то делал отправку писем через гугл таблицы? Т.е, проворачивал ли, кто-то вот такую схему "сайт -> гугл таблица -> почта" при помощи js?
Именно такая схема не имеет смысла, есть смысл просто параллельно организовать в коде отправку и письма, и лида в GD. Для работы с последним вы также можете найти множество примеров реализации в сети, включая документацию от самого Google