Wykrywanie użytkownika wstawiającego wiersz lub kolumnę w arkuszu kalkulacyjnym google i reagującego w skrypcie

Google dodał Zdarzenie "przy zmianie", które wykrywa wstawianie/usuwanie wierszy/kolumn wraz z innymi typami zmian, typy, dla których możesz zobaczyć tutaj pod dozwolonymi wartościami dla changeType. Poniżej znajdują się instrukcje z tutaj opisujące jak dodać wyzwalacz do projektu, aby można było wywołać funkcję, gdy wystąpi zdarzenie "On Change".

Aby ręcznie utworzyć instalowalny WYZWALACZ za pomocą okna dialogowego w Edytorze skryptów, wykonaj następujące czynności kroki:

1. z Edytora skryptów wybierz Edytuj > wyzwalacze bieżącego projektu.
2. kliknij w link, który mówi: nie ma ustawionych wyzwalaczy. Kliknij tutaj, aby dodać teraz .
3. W polu Uruchom wybierz nazwę funkcji, którą chcesz uruchomić.
4. w sekcji zdarzenia wybierz Time-driven lub aplikację Google, do której skrypt jest przypisany (na przykład z arkusza kalkulacyjnego ).
5. Wybierz i skonfiguruj Typ wyzwalacza, który chcesz utworzyć(na przykład timer Hour, który uruchamia co godzinę lub przy otwartym wyzwalaczu).
6. Opcjonalnie kliknij powiadomienia , aby skonfigurować, jak i kiedy skontaktujemy się z tobą przez e-mail, jeśli aktywowana funkcja zawiedzie.
7. Kliknij Zapisz.

W kroku 4 Wybierz z arkusza kalkulacyjnego , a w kroku 5 Wybierz przy zmianie. To powinno mieć efekt, którego szukasz. Istnieją również opcje dodawania wyzwalaczy programowo i żądania autoryzacji, jeśli próbujesz użyć tego w dodatku, który ma być rozpowszechniany wśród użytkowników. Oba są szczegółowo opisane w dokumentacji Instalowalnych wyzwalaczy .

Jest jeszcze inny sposób, z którego ostatnio korzystałem. Za każdym razem, gdy uruchomiona zostanie metoda onEdit, zwraca ona obiekt zdarzenia (e), który dostarcza cennych informacji o tym, co się dzieje.

Na przykład daje zakres, który można pobrać z e. range. Stamtąd można przejść na wiele różnych sposobów i dowiedzieć się, na przykład, który wiersz jest edytowany. Ale w obiekcie e jest też więcej przydatnych danych. Daje Ci "oldvalue" (e. oldValue ) edytowanej komórki oraz nową wartość (E. wartość ).

Jednym z możliwych sposobów mieszania wszystkich tych informacji byłoby uzyskanie zakresu odpowiadającego edytowanemu wierszowi, a następnie sprawdzenie, czy komórki są puste (ale te, które właśnie edytowałeś) i czy nie ma oldValue.

To niekoniecznie odpowiada ostatniemu wierszowi arkusza kalkulacyjnego, ale pustemu wierszowi. Jeśli jesteś zgodny z tym, jak wypełniasz swoje dane, może to działać dla Ciebie:

//val = inserted value (e.value);
//old = old Value (e.oldValue);
//col = number of column being edited
//arr = array with the indexes of the columns that should be completed so as to make a new row [0,1,2...n]
function isInsert(old, val, col, arr){
   if((typeof val != "object")&&!old&&(arr.some(isNotEmpty, col)))
     return true;
   else
     return false;
}

function isNotEmpty(el){
   if(this == el)
     return true;
}

Miałem z tym problem, dopóki nie dałem uprawnień skryptowi. W przeciwnym razie funkcjonalność PropertiesService nie będzie działać. Kiedy już to zrobiłem, byłem w stanie wykryć, który wiersz został wstawiony za pomocą następującego kodu:

var props = PropertiesService.getUserProperties();

function onEdit(e) {
  props.setProperty("firstRow", e.range.getRow());
  props.setProperty("lastRow", e.range.getLastRow());
}


function onChange(e){
if(e.changeType=="INSERT_ROW")
    SpreadsheetApp.getUi().alert("Inserted Rows: " +
                                 props.getProperty("firstRow") + 
                                 " - " +
                                 props.getProperty("lastRow"));
}

Bawiłem się z onEdit i onChange. Odpowiedź onEdit umożliwia dostęp do wierszy, które zostały edytowane. Niestety odpowiedź onChange nie pozwala na to. Tak więc, aby uzyskać solidne rozwiązanie, wygląda na to, że musisz odwołać się do obu wyzwalaczy. Jeśli arkusz nie wymaga pustych wierszy/kolumn, poniższy skrypt usuwa wszystkie nowo dodane wiersze / kolumny, usuwa wszystkie puste wiersze/kolumny( w przypadku, gdy użytkownik masowo dodał wiersze / kolumny), a następnie ostrzega użytkownika, że nie może dodać wiersze lub kolumny:

//////////////////////
// Global Variables //
//////////////////////

var SHEET = SpreadsheetApp.getActiveSheet();
var PROPERTIES = PropertiesService.getScriptProperties();

////////////////////
// Event Triggers //
////////////////////

/**
 * Track original sheet row/column count and register onChange trigger.
 */
function onOpen()
{
    // Set original dimensions
    PROPERTIES.setProperty('rows', SHEET.getMaxRows());
    PROPERTIES.setProperty('columns', SHEET.getMaxColumns());

    // Create onChange trigger
    ScriptApp
        .newTrigger('deleteNewRowsAndColumns')
        .forSpreadsheet(SpreadsheetApp.getActive())
        .onChange()
        .create();
}

/**
 * If new rows or columns were added to the sheet
 * warn the user that they cannot perform these
 * actions and delete empty (new) rows and columns.
 *
 * @param e
 */
function deleteNewRowsAndColumns(e)
{
    switch(e.changeType) {
        case 'INSERT_COLUMN':
            removeEmptyColumns();
            warn();
            break;
        case 'INSERT_ROW':
            removeEmptyRows();
            warn();
            break;
        default:
            return
    }
}

///////////////
// Utilities //
///////////////

/**
 * Remove empty columns.
 *
 * This function assumes you have a header row in which
 * all columns should have a value. Change headerRow value
 * if your headers are not in row 1.
 */
function removeEmptyColumns() {
    var maxColumns = SHEET.getMaxColumns();
    var lastColumn = SHEET.getLastColumn();
    if (maxColumns - lastColumn != 0) {
        // New column(s) were added to the end of the sheet.
        SHEET.deleteColumns(lastColumn + 1, maxColumns - lastColumn);
    } else {
        // New column was added in the middle of the sheet.
        // Start from last column and work backwards, delete
        // first column found with empty header cell.
        var headerRow = 1;
        var headers =  SHEET.getRange(headerRow, 1, 1, lastColumn).getValues()[0];
        for (var col = lastColumn; col >= 1; col--) {
            if (headers[col -1] == '') {
                SHEET.deleteColumn(col);
                // Since can only insert one column to the left
                // or right at a time, can safely exit here;
                break;
            }
        }
    }
}

/**
 * Remove empty rows.
 *
 * This function assumes that all rows should
 * have data in the first cell.
 */
function removeEmptyRows() {
    var maxRows = SHEET.getMaxRows();
    var lastRow = SHEET.getLastRow();
    if (maxRows-lastRow != 0) {
        // New row(s) were added to the end of the sheet.
        SHEET.deleteRows(lastRow + 1, maxRows - lastRow);
    } else {
        // New row was added in the middle of the sheet.
        // Start from last column and work backwards, delete
        // first empty column found.
        var values = SHEET.getRange('A:A').getValues();
        var startIndex = values.length - 1;
        for (var i = startIndex; i >= 0; i--) {
            if (values[i] && values[i][0] == '') {
                SHEET.deleteRow(i + 1);
                // User can bulk add rows to the bottom of the file
                // but can only add 1 above or below at a time in the
                // middle of the file, so it's safe to exit here.
                break;
            }
        }
    }
}

/**
 * Return user warning message about adding new rows and columns
 */
function warn()
{
    SpreadsheetApp.getUi().alert('You cannot add new rows or columns.');
}