Блог

Я создал следующую простую функцию:

 function test(r,c) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(r,c).setBackground("red");
  return 1;
}
  

В электронной таблице я пишу «=test(строка (), столбец ())

Это приводит к ОШИБКЕ со следующим сообщением: Ошибка: у вас нет разрешения на вызов setBackground (строка 3).

Это НЕ проблема, если я создам другой вызов функции в скрипте следующим образом:

 function test_the_test(){
  test(5,4); 
}
  

Почему я не могу вызвать тестовую функцию из ячейки электронной таблицы?

Заранее спасибо

Как это четко объяснено в документации, пользовательские функции возвращают значения, но они не могут устанавливать значения за пределами ячеек, в которых они находятся. В большинстве случаев пользовательская функция в ячейке A1 не может изменить ячейку A5.Это, конечно, также верно и для других методов, таких как setBackground и т. Д.

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

На самом деле это довольно просто. Определенно, это неправда, что вы не можете изменять содержимое других ячеек.

Хитрость заключается в том, чтобы не вызывать функцию из ячейки, а монтировать ее в чертеж / изображение.

1. Нажмите вставить -> рисование
2. Нарисуйте изображение и сохраните его (вы должны увидеть свое изображение в электронной таблице)
3. Щелкните по нему правой кнопкой мыши — в правом верхнем углу есть маленький треугольник, открывающий параметры
4. Нажмите Назначить скрипт и введите имя вашего скрипта без круглых скобок (например, «test», а не «test ()») и подтвердите
5. Нажмите кнопку. Появляется всплывающее окно с запросом прав доступа к электронной таблице.
6. Подтвердите это, если возникает проблема с обновлением, просто обновите его вручную (F5)
7. Теперь вы можете нажать кнопку и отредактировать любую ячейку, которая вам нравится

Этот код отлично работает при подключении к кнопке.

 function test() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(3,5).setBackground("red");
}
  

Текущая версия Google Sheets (январь 2022) использует редактор сценариев приложений и позволяет помещать скрипты в файл контейнера, который автоматически прикрепляется к вашему Google Sheet.

Вы можете открыть редактор сценариев приложений из меню Google Sheets в разделе Extensions -> App Script

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

 function onEdit(e) {
  if( ! e ){
    return;
  }
  var currentSheet = e.source.getActiveSheet();
  var currentRange = e.range;
  // only want action to occur when a single cell changes
  if( currentRange.getNumRows() === 1 amp;amp; currentRange.getNumColumns() === 1 ){
    var currentColumn = currentRange.getLastColumn();
    var currentRow = currentRange.getLastRow();
    // only want action to occur for a column on a certain sheet
    var myTargetSheet = "Sheet 1";
    var myTargetColumn = 1;
    if( currentSheet.getName() == myTargetSheet amp;amp; currentColumn == myTargetColumn ){
      // set background color for the selected row based on a lookup
      var cellValue = currentRange.getCell(1,1).getValue();
      var assignedColor = myCustomSearch( cellValue );
      currentSheet.getRange( "A"   currentRow   ":E"   currentRow ).setBackgroundColor( assignedColor );
    }
  }
}

function myCustomSearch( searchTerm ){
  var assignedColor = "#ffffff";
  var lookupSheet = SpreadsheetApp.getActive().getSheetByName("Sheet 2");
  var lookupRange = lookupSheet.getRange("H1:H20"); // where the search terms live
  var numColumns = lookupRange.getNumColumns();
  var numRows = lookupRange.getNumRows();
  var lookupList = lookupRange.getValues();
  for( var myColumn=0; myColumn < numColumns; myColumn   ){
    for( var myRow=0; myRow < numRows; myRow   ){
      if( lookupList[myRow][myColumn] == searchTerm ){
        assignedColor = lookupSheet.getRange("H" (myRow 1)).getBackgroundColor();
        break;
      }
    }
  }
  return assignedColor;
}