Google Таблицы - Скрипт для изменения значения ячейки на основе значений нескольких ячеек

Некоторое время я искал и пытался вместе работать над сценарием из различных тем, на которые были даны ответы, который позволит мне настроить содержимое соседних ячеек на основе введенных данных. Кажется, я не могу заставить его работать должным образом, и мне нужна помощь, чтобы направить корабль в правильном направлении. Вот чего я пытаюсь достичь:

--Если значение ячейки A2: A представляет собой шестизначное число И значение ячейки D2: D (та же строка) равно «MATCH», тогда значение ячейки B2: B должно быть установлено на «ANN». значение ячейки A2: A - шестизначное число, И значение ячейки D2: D (та же строка) - «NO MATCH», тогда значение для ячейки B2: B должно быть установлено на «ANN» и выпадающий список проверки данных список ['ANN', 'RNW'] заполнить со значением по умолчанию для списка, установленным на «ANN» - Если значение ячейки A2: A имеет длину семи или более символов, то раскрывающийся список проверки данных of ['1DY', 'RNW', 'NEW'] заполнить со значением по умолчанию для списка, установленным на "1DY"

Можно ли вообще установить значение ячейки проверки данных на конкретное значение по умолчанию? Это важно, поскольку когда пользователь вводит данные, он, скорее всего, примет значение по умолчанию. Если им не нужно значение по умолчанию, они могут выбрать значение из раскрывающегося списка.

Я создал тестовый лист, который показывает, как должен выглядеть лист, когда данные заполняются в столбце A, а соответствующие значения - в столбце B.

Мой тест здесь: https://docs.google.com/spreadsheets/d/1p8sq63S-vSU1FKFLjtr2ZypItN5viXotoZL0Ki2PoQM/edit?usp=sharing

Вот сценарий, который я пытался создать (я тоже считаю его забавным). Это моя первая попытка настроить скрипт Google для работы с электронной таблицей.

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var aSheet = ss.getActiveSheet();
  var aCell = aSheet.getActiveCell();
  var aColumn = aCell.getColumn();
  var aRow = aCell.getRow();
  //var licenseStatus = aSheet.getRange(aRow, aColumn+9).getValue();

 // The row and column here are relative to the range
 // getCell(1,1) in this code returns the cell at B2, B2
  var licenseTypeCell = aSheet.getRange(aRow, aColumn+1);

  if (aColumn == 1 && aSheet.getName() == 'Onsite') {
    if (isnumber(aCell) && (len(aCell) <= 6)) {
      var rule = SpreadsheetApp.newDataValidation().requireValueInList(['ANN','RNW']).build();
      licenseTypeCell.setValue("ANN");
      licenseTypeCell.setDataValidation(rule);
    } else {
      var rule = SpreadsheetApp.newDataValidation().requireValueInList(['1DY','RNW','NEW']).build();
      licenseTypeCell.setValue("1DY");
      licenseTypeCell.setDataValidation(rule);
    }
  }
}

Любая помощь / руководство будут очень благодарны.


google-sheets google-sheets-api spreadsheet google-spreadsheet-api
person Jeff Lucido    schedule 06.02.2018    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вы на правильном пути, небольшие изменения. Ниже вы найдете некоторые новые функции, которые будут использоваться в вашем коде.

1) getValue () Вы получаете свою ячейку, используя var aCell = aSheet.getActiveCell(), т.е. ячейку, которая была отредактирована. Но чтобы получить значение ячейки, вам нужно будет сделать следующее: aValue = aCell.getValue()

2) isNaN () Чтобы проверить, является ли aValue (как определено выше) числом или нет. Вы будете использовать функцию под названием isNaN(aValue). Сценарий Google использует платформу javascript, поэтому нам необходимо использовать функции из javascript. Это отличается от встроенной функции, которую вы используете в электронной таблице Google. Он возвращает Истина, если значение равно Не число (NAN). Следовательно, мы используем оператор not (!), чтобы перевернуть возвращаемое значение, например

if(!isNaN(aValue))

3) Количество цифр В скриптах Google нет функции len, поэтому, чтобы определить, состоит ли номер из 6 цифр, вы можете сделать следующее

 if(aValue < 1000000)

Ваш окончательный код будет выглядеть примерно так:

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var aSheet = ss.getActiveSheet();
  var aCell = aSheet.getActiveCell();
  var aColumn = aCell.getColumn();
  var aRow = aCell.getRow();
  //var licenseStatus = aSheet.getRange(aRow, aColumn+9).getValue();

 // The row and column here are relative to the range
 // getCell(1,1) in this code returns the cell at B2, B2
  var licenseTypeCell = aSheet.getRange(aRow, aColumn+1);
  var aValue = aCell.getValue()
  if (aColumn == 1 && aSheet.getName() == 'Main') {
    if (!isNaN(aValue) && aValue < 1000000) {
      var matchCell = aSheet.getRange(aRow, aColumn+3).getValue()
      //The above gets value of column D (MATCH or NO MATCH)
      if(matchCell == "MATCH"){ //Check if Col D is MATCH
        licenseTypeCell.setValue("ANN");
      }
      else{
      var rule = SpreadsheetApp.newDataValidation().requireValueInList(['ANN','RNW']).build();
      licenseTypeCell.setValue("ANN");
      licenseTypeCell.setDataValidation(rule);
      }
    } else {
      var rule = SpreadsheetApp.newDataValidation().requireValueInList(['1DY','RNW','NEW']).build();
      licenseTypeCell.setValue("1DY");
      licenseTypeCell.setDataValidation(rule);
    }
  }
}

Также обратите внимание на добавление следующих строк для проверки значения столбца D.

var matchCell = aSheet.getRange(aRow, aColumn+3).getValue()
//The above gets value of column D (MATCH or NO MATCH)
if(matchCell == "MATCH"){ //Check if Col D is MATCH
 licenseTypeCell.setValue("ANN");
}
person Jack Brown    schedule 06.02.2018
comment
Это фантастика! Спасибо, Джек, за быстрый ответ, а также за подробное объяснение функции. Прошло довольно много времени с тех пор, как я работал с Javascript, поэтому я был удивлен, что оказался ближе, чем я думал. Я немного поработал, чтобы добавить некоторые дополнительные условия, о которых подумал с момента моей последней публикации. Еще раз спасибо за помощь. - person Jeff Lucido; 07.02.2018