Rozszerzenie Arkuszy Google

Używaj Google Apps Script, aby rozszerzać funkcje Arkuszy. Dodawanie niestandardowych menu, okien i pasków bocznych w Arkuszach. Pisz funkcje niestandardowe do Arkuszy i integruj je z innymi usługami Google, takimi jak Kalendarz Google, Dysk Google i Gmail.

Większość skryptów przeznaczonych do Arkuszy manipuluje tablicami, aby wchodzić w interakcje z komórkami, wierszami i kolumnami w arkuszu kalkulacyjnym. Jeśli nie znasz tablic w JavaScript, Codecademy oferuje świetny moduł szkoleniowy dotyczący tablic. Ten kurs nie został opracowany przez Google i nie jest z nim powiązany.

Krótkie wprowadzenie do korzystania z Apps Script w Arkuszach znajdziesz w 5-minutowym przewodniku Makra, menu i funkcje niestandardowe.

Rozpocznij

Apps Script zawiera specjalne interfejsy API, które umożliwiają zautomatyzowane tworzenie, odczytywanie i edytowanie arkuszy. Apps Script wchodzi w interakcję z Arkuszami na 2 sposoby: każdy skrypt może tworzyć lub modyfikować arkusz kalkulacyjny, jeśli użytkownik skryptu ma odpowiednie uprawnienia, a skrypt może być też powiązany z arkuszem kalkulacyjnym. Skrypty powiązane mają specjalne możliwości zmiany interfejsu użytkownika lub reagowania na otwarcie arkusza kalkulacyjnego. Aby utworzyć skrypt powiązany, w Arkuszach wybierz Rozszerzenia > Apps Script.

Usługa arkuszy kalkulacyjnych traktuje Arkusze jako siatkę, działając na dwuwymiarowych tablicach. Aby pobrać dane z arkusza kalkulacyjnego, uzyskaj dostęp do arkusza, w którym są przechowywane dane, uzyskaj zakres, który zawiera dane, a następnie pobierz wartości komórek. Apps Script ułatwia dostęp do danych, odczytując dane strukturalne w arkuszu kalkulacyjnym i tworząc dla nich obiekty JavaScript.

Odczytywanie danych

Załóżmy, że masz listę nazw i numerów produktów przechowywaną w arkuszu kalkulacyjnym, jak pokazano na ilustracji poniżej.

Poniższy przykład pokazuje, jak pobrać i zarejestrować nazwy produktów i numery produktów.

function logProductInfo() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  for (let i = 0; i < data.length; i++) {
    Logger.log('Product name: ' + data[i][0]);
    Logger.log('Product number: ' + data[i][1]);
  }
}

Wyświetl logi

Aby wyświetlić zarejestrowane dane, u góry edytora skryptów kliknij Dziennik wykonania.

Zapisywanie danych

Aby zapisać dane, np. nową nazwę produktu i numer w arkuszu kalkulacyjnym, dodaj na końcu skryptu ten kod:

function addProduct() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}

Powyższy kod dodaje nowy wiersz u dołu arkusza kalkulacyjnego z określonymi wartościami. Jeśli uruchomisz tę funkcję, do arkusza zostanie dodany nowy wiersz.

Menu niestandardowe i interfejsy

Dostosowywanie Arkuszy przez dodawanie niestandardowych menu, okien i pasków bocznych. Aby poznać podstawy tworzenia menu, zapoznaj się z przewodnikiem po menu. Więcej informacji o dostosowywaniu treści okna znajdziesz w przewodniku po usłudze HTML.

Dołącz funkcję skryptu do obrazu lub rysunku w arkuszu kalkulacyjnym. Funkcja jest wykonywana, gdy użytkownik kliknie obraz lub rysunek. Więcej informacji znajdziesz w artykule Grafika i Rysunki w Arkuszach.

Jeśli planujesz opublikować niestandardowy interfejs jako dodatek, postępuj zgodnie z przewodnikiem po stylach, aby zachować spójność ze stylem i układem edytora Arkuszy.

Łączenie z Formularzami Google

Połącz Formularze Google z Arkuszami za pomocą usług Formularze i Arkusze. Ta funkcja automatycznie tworzy formularz Google na podstawie danych w arkuszu kalkulacyjnym. Apps Script umożliwia też używanie wyzwalaczy, takich jak onFormSubmit, do wykonywania określonych działań po tym, jak użytkownik odpowie na formularz. Aby dowiedzieć się więcej o łączeniu Arkuszy z Formularzami, wypróbuj 5-minutowy przewodnik Zarządzanie odpowiedziami w Formularzach.

Formatowanie danych

Klasa Range ma metody takie jak setBackground, które umożliwiają dostęp do formatu komórki lub zakresu komórek i jego modyfikowanie. Poniższy przykład ustawia styl czcionki zakresu:

function formatMySpreadsheet() {
  // Set the font style of the cells in the range of B2:C2 to be italic.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const cell = sheet.getRange('B2:C2');
  cell.setFontStyle('italic');
}

Walidacja danych

Uzyskuj dostęp do istniejących reguł weryfikacji danych w Arkuszach lub twórz nowe reguły. Na przykład poniższy przykład pokazuje, jak ustawić regułę sprawdzania poprawności danych, która zezwala na wpisywanie w komórce tylko liczb z zakresu od 1 do 100.

function validateMySpreadsheet() {
  // Set a rule for the cell B4 to be a number between 1 and 100.
  const cell = SpreadsheetApp.getActive().getRange('B4');
  const rule = SpreadsheetApp.newDataValidation()
     .requireNumberBetween(1, 100)
     .setAllowInvalid(false)
     .setHelpText('Number must be between 1 and 100.')
     .build();
  cell.setDataValidation(rule);
}

Więcej informacji o pracy z regułami sprawdzania poprawności danych znajdziesz w artykułach SpreadsheetApp.newDataValidation, DataValidationBuilder i Range.setDataValidation.

Wykresy

Umieść w arkuszu kalkulacyjnym wykresy przedstawiające dane z określonego zakresu. W tym przykładzie generowany jest umieszczony wykres słupkowy, przy założeniu, że w komórkach A1:B15 znajdują się dane, na podstawie których można utworzyć wykres:

function newChart() {
  // Generate a chart representing the data in the range of A1:B15.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];

  const chart = sheet.newChart()
     .setChartType(Charts.ChartType.BAR)
     .addRange(sheet.getRange('A1:B15'))
     .setPosition(5, 5, 0, 0)
     .build();

  sheet.insertChart(chart);
}

Więcej informacji o osadzaniu wykresu w arkuszu kalkulacyjnym znajdziesz w artykule EmbeddedChart i w przypadku konkretnych narzędzi do tworzenia wykresów, np. EmbeddedPieChartBuilder.

Funkcje niestandardowe w Arkuszach Google

Funkcja niestandardowa jest podobna do wbudowanej funkcji arkusza kalkulacyjnego, takiej jak =SUM(A1:A5), z tym wyjątkiem, że zachowanie funkcji definiujesz za pomocą Apps Script. Możesz na przykład utworzyć funkcję niestandardową, in2mm(), która przekształca wartość z cali na milimetry, a następnie użyć formuły w arkuszu kalkulacyjnym, wpisując w komórce =in2mm(A1) lub =in2mm(10).

Aby dowiedzieć się więcej o funkcjach niestandardowych, wypróbuj 5-minutowy przewodnik Menu i funkcje niestandardowe lub zapoznaj się z bardziej szczegółowym przewodnikiem po funkcjach niestandardowych.

Makra

Makra to kolejny sposób na uruchamianie kodu Apps Script z interfejsu Arkuszy. W przeciwieństwie do funkcji niestandardowych aktywujesz je za pomocą skrótu klawiszowego lub w menu Arkuszy. Więcej informacji znajdziesz w artykule Makra w Arkuszach.

Dodatki do Arkuszy Google

Dodatki to specjalnie spakowane projekty Apps Script, które działają w Arkuszach i można je zainstalować ze sklepu z dodatkami do Arkuszy. Jeśli masz skrypt do Arkuszy i chcesz go udostępnić, Apps Script umożliwia opublikowanie skryptu jako dodatku, aby inni użytkownicy mogli go zainstalować.

Wydajność i skalowanie

Wraz ze wzrostem liczby zbiorów danych mogą pojawiać się problemy z wydajnością. Aby zoptymalizować arkusz kalkulacyjny i skrypty:

Postępuj zgodnie ze sprawdzonymi metodami: przeczytaj przewodnik ze sprawdzonymi metodami, aby poznać wskazówki dotyczące minimalizowania liczby wywołań usług i korzystania z operacji wsadowych.
Optymalizuj formuły: jeśli arkusz kalkulacyjny działa wolno z powodu złożonych formuł (np. VLOOKUP, ARRAYFORMULA lub IMPORTRANGE), rozważ użycie Apps Script do wykonywania tych obliczeń w pamięci i zapisywania wyników w partiach.
Rozważ alternatywne bazy danych: w przypadku bardzo dużych zbiorów danych (zbliżających się do 10 milionów komórek) lub wprowadzania danych z dużą częstotliwością (np. w przypadku wielu połączonych formularzy) rozważ użycie Google Cloud SQL z JDBC lub BigQuery.

Aktywatory

Skrypty powiązane z plikiem Arkuszy mogą używać prostych wyzwalaczy, takich jak funkcje onOpen() i onEdit(), aby automatycznie reagować, gdy użytkownik, który ma dostęp do edycji arkusza kalkulacyjnego, otworzy lub zmodyfikuje ten arkusz. Podobnie jak proste aktywatory, aktywatory z możliwością zainstalowania umożliwiają Arkuszom automatyczne uruchamianie funkcji, gdy wystąpi określone zdarzenie. Triggery instalowane są jednak bardziej elastyczne niż triggery proste i obsługują te zdarzenia: otwarcie, edycja, zmiana, przesłanie formularza i zdarzenia oparte na czasie (zegar).

Rozszerzenie Arkuszy Google Zadbaj o dobrą organizację dzięki kolekcji Zapisuj i kategoryzuj treści zgodnie ze swoimi preferencjami.