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ą automatyczne 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 produktów 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-minutowe Zarządzanie odpowiedziami w Formularzach krótkie wprowadzenie.
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 kod 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-minutowe krótkie wprowadzenie 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,ARRAYFORMULAlubIMPORTRANGE), 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 reguły, reguły instalowane 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).