Rozszerzenie Arkuszy Google

Używanie Google Apps Script do rozszerzania możliwości Arkuszy Dodawanie niestandardowych menu, okien i pasków bocznych w Arkuszach Pisanie niestandardowych funkcji dla Arkuszy i integrowanie ich 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 ani nie jest z nim powiązany.

Aby szybko zapoznać się z używaniem Apps Script w Arkuszach, przeczytaj 5-minutowy przewodnik dla początkujących dotyczący makr, menu i funkcji niestandardowych.

Rozpocznij

Apps Script zawiera specjalne interfejsy API, które umożliwiają zautomatyzowane tworzenie, odczytywanie i edytowanie Arkuszy. Apps Script wchodzi w interakcje 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 też być powiązany z arkuszem kalkulacyjnym. Powiązane skrypty mają specjalne możliwości zmiany interfejsu użytkownika lub reagowania na otwarcie arkusza kalkulacyjnego. Aby utworzyć powiązany skrypt, w Arkuszach wybierz Rozszerzenia > Apps Script.

Usługa arkusza kalkulacyjnego traktuje Arkusze jako siatkę, która działa z tablicami dwuwymiarowymi. Aby pobrać dane z arkusza kalkulacyjnego, uzyskaj dostęp do arkusza, w którym są przechowywane dane, pobierz zakres zawierający dane, a następnie pobierz wartości komórek. Apps Script ułatwia dostęp do danych, odczytując uporządkowane dane w arkuszu kalkulacyjnym i tworząc dla nich obiekty w JavaScripcie.

Odczytywanie danych

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

Poniższy przykład pokazuje, jak pobrać i zarejestrować nazwy 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ę i numer produktu, w arkuszu kalkulacyjnym, dodaj ten kod na końcu skryptu.

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

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

Niestandardowe menu i interfejsy użytkownika

Dostosuj Arkusze, dodając niestandardowe menu, okna i paski boczne. Aby poznać podstawy tworzenia menu, przeczytaj przewodnik po menu. Aby dowiedzieć się, jak dostosować zawartość okna, przeczytaj przewodnik po usłudze HTML.

Dołącz funkcję skryptu do obrazu lub rysunku w arkuszu kalkulacyjnym. Funkcja zostanie wykonana, 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 część dodatku, postępuj zgodnie z przewodnikiem po stylu, 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 Arkusz kalkulacyjny. Ta funkcja automatycznie tworzy Formularz Google na podstawie danych w arkuszu kalkulacyjnym. Apps Script umożliwia też używanie aktywatorów, np. 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, przeczytaj 5-minutowy przewodnik dla początkujących dotyczący zarządzania 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

Uzyskaj dostęp do istniejących reguł sprawdzania poprawności danych w Arkuszach lub utwó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

Osadzaj w arkuszu kalkulacyjnym wykresy, które przedstawiają dane z określonego zakresu. Poniższy przykład generuje osadzony wykres słupkowy, zakładając, że w komórkach A1:B15 znajdują się dane, które można przedstawić na wykresie:

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 artykułach EmbeddedChart i konkretnych narzędziach do tworzenia wykresów, np. EmbeddedPieChartBuilder.

Funkcje niestandardowe w Arkuszach Google

Funkcja niestandardowa jest podobna do wbudowanej funkcji arkusza kalkulacyjnego , np. =SUM(A1:A5), z tą różnicą, że jej działanie definiujesz za pomocą Apps Script. Możesz na przykład utworzyć funkcję niestandardową in2mm(), która konwertuje 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, przeczytaj 5-minutowy przewodnik dla początkujących dotyczący menu i funkcji niestandardowych lub zapoznaj się z bardziej szczegółowym przewodnikiem po funkcjach niestandardowych.

Makra

Makra to kolejny sposób na wykonywanie kodu Apps Script z interfejsu Arkuszy. W przeciwieństwie do funkcji niestandardowych aktywujesz je za pomocą skrótu klawiszowego lub 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 dla Arkusz i chcesz się nim podzielić, Apps Script umożliwia opublikowanie go jako dodatku, aby inni użytkownicy mogli go zainstalować.

Wydajność i skalowanie

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

Postępuj zgodnie ze sprawdzonymi metodami: przeczytaj przewodnik Sprawdzone metody , aby uzyskać wskazówki dotyczące minimalizowania wywołań usług i używania operacji zbiorczych.
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. wiele połączonych formularzy) rozważ użycie Google Cloud SQL za pomocą JDBC lub BigQuery.

Aktywatory

Skrypty, które są powiązane z plikiem Arkuszy , mogą używać prostych aktywatorów, takich jak funkcje onOpen() i onEdit(), aby automatycznie reagować, gdy użytkownik, który ma uprawnienia do edycji arkusza kalkulacyjnego, otworzy lub edytuje arkusz kalkulacyjny. Podobnie jak proste aktywatory, aktywatory instalowane umożliwiają Arkuszom automatyczne uruchamianie funkcji, gdy wystąpi określone zdarzenie. Aktywatory instalowane są jednak bardziej elastyczne niż proste aktywatory i obsługują te zdarzenia: otwarcie, edycja, zmiana, przesłanie formularza i aktywacja czasowa (zegar).

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