Informacje o tym ćwiczeniu (w Codelabs)
1. Wprowadzenie
Witamy w czwartej części playlisty z ćwiczeniami dotyczącymi podstaw Apps Script w Arkuszach Google.
Po ukończeniu tego ćwiczenia dowiesz się, jak formatować dane arkusza kalkulacyjnego w Apps Script i pisać funkcje do tworzenia uporządkowanych arkuszy kalkulacyjnych pełnych sformatowanych danych pobranych z publicznego interfejsu API.
Czego się nauczysz
- Jak stosować różne operacje formatowania Arkuszy Google w Apps Script.
- Jak przekształcić listę obiektów JSON i ich atrybutów w uporządkowany arkusz danych za pomocą Apps Script.
Zanim zaczniesz
To czwarte szkolenie z serii Podstawy Apps Script w Arkuszach Google. Zanim zaczniesz to ćwiczenie, wykonaj poprzednie ćwiczenia:
Czego potrzebujesz
- znajomość podstawowych tematów dotyczących Apps Script, które zostały omówione w poprzednich ćwiczeniach z tej playlisty;
- Podstawowa znajomość edytora Apps Script
- Podstawowa znajomość Arkuszy Google.
- Możliwość odczytywania arkuszy w notacji A1.
- Podstawowa znajomość JavaScriptu i jego klasy
String
2. Skonfiguruj
Zanim przejdziesz dalej, musisz mieć arkusz kalkulacyjny z danymi. Podobnie jak wcześniej udostępniliśmy arkusz danych, który możesz skopiować na potrzeby tych ćwiczeń. Wykonaj te czynności:
- Kliknij ten link, aby skopiować arkusz danych, a potem kliknij Utwórz kopię. Nowy arkusz kalkulacyjny zostanie umieszczony w folderze Dysku Google i będzie nosił nazwę „Kopia formatowania danych”.
- Kliknij tytuł arkusza kalkulacyjnego i zmień go z „Kopia formatowania danych” na „Formatowanie danych”. Arkusz powinien wyglądać tak jak poniżej i zawierać podstawowe informacje o pierwszych 3 filmach z serii Gwiezdne Wojny:
- Aby otworzyć edytor skryptów, wybierz Rozszerzenia> Apps Script.
- Kliknij tytuł projektu Apps Script i zmień go z „Projekt bez tytułu” na „Formatowanie danych”. Aby zapisać zmianę tytułu, kliknij Zmień nazwę.
Dzięki temu arkuszowi kalkulacyjnemu i projektowi możesz rozpocząć ćwiczenie z programowania. Przejdź do następnej sekcji, aby dowiedzieć się więcej o podstawowym formatowaniu w Apps Script.
3. Tworzenie menu niestandardowego
W Arkuszach możesz zastosować kilka podstawowych metod formatowania w Apps Script. Poniższe ćwiczenia pokazują kilka sposobów formatowania danych. Aby ułatwić sobie kontrolowanie działań związanych z formatowaniem, utwórzmy niestandardowe menu z potrzebnymi elementami. Proces tworzenia menu niestandardowych został opisany w ćwiczeniach z programowania Praca z danymi, ale podsumujemy go tutaj jeszcze raz.
Implementacja
Utwórzmy menu niestandardowe.
- W edytorze Apps Script zastąp kod w projekcie skryptu tym kodem:
/**
* A special function that runs when the spreadsheet is opened
* or reloaded, used to add a custom menu to the spreadsheet.
*/
function onOpen() {
// Get the spreadsheet's user-interface object.
var ui = SpreadsheetApp.getUi();
// Create and add a named menu and its items to the menu bar.
ui.createMenu('Quick formats')
.addItem('Format row header', 'formatRowHeader')
.addItem('Format column header', 'formatColumnHeader')
.addItem('Format dataset', 'formatDataset')
.addToUi();
}
- Zapisz projekt skryptu.
- W edytorze skryptów wybierz
onOpenz listy funkcji i kliknij Uruchom. Spowoduje to uruchomienie usługionOpen()w celu ponownego utworzenia menu arkusza kalkulacyjnego, dzięki czemu nie musisz ponownie wczytywać arkusza.
Weryfikacja kodu
Przyjrzyjmy się temu kodowi, aby zrozumieć, jak działa. W onOpen() pierwsza linia używa metody getUi() do uzyskania obiektu Ui reprezentującego interfejs aktywnego arkusza kalkulacyjnego, z którym jest powiązany ten skrypt.
Kolejne wiersze tworzą menu (Quick formats), dodają do niego elementy (Format row header, Format column header i Format dataset), a następnie dodają menu do interfejsu arkusza kalkulacyjnego. Służą do tego odpowiednio metody createMenu(caption), addItem(caption, functionName) i addToUi().
Metoda addItem(caption, functionName) tworzy połączenie między etykietą elementu menu a funkcją Apps Script, która jest uruchamiana po wybraniu elementu menu. Na przykład wybranie pozycji menu Format row header spowoduje, że Arkusze spróbują uruchomić funkcję formatRowHeader() (która jeszcze nie istnieje).
Wyniki
W arkuszu kalkulacyjnym kliknij menu Quick formats, aby wyświetlić nowe pozycje menu:
Kliknięcie tych elementów powoduje błąd, ponieważ nie zostały jeszcze zaimplementowane odpowiadające im funkcje. Zróbmy to teraz.
4. Formatowanie wiersza nagłówka
Zbiory danych w arkuszach kalkulacyjnych często mają wiersze nagłówków, które identyfikują dane w poszczególnych kolumnach. Warto sformatować wiersze nagłówka, aby wizualnie odróżnić je od pozostałych danych w arkuszu kalkulacyjnym.
W pierwszym codelabie utworzyliśmy makro dla nagłówka i dostosowaliśmy jego kod. W tym przykładzie sformatujesz wiersz nagłówka od zera za pomocą Apps Script. Utworzony wiersz nagłówka spowoduje pogrubienie tekstu nagłówka, zmianę koloru tła na ciemny niebieskozielony, zmianę koloru tekstu na biały i dodanie linii obramowania.
Implementacja
Aby wdrożyć operację formatowania, użyjesz tych samych metod usługi Arkusze kalkulacyjne, co wcześniej, ale teraz skorzystasz też z niektórych metod formatowania tej usługi. Wykonaj te czynności:
- W edytorze Apps Script dodaj tę funkcję na końcu projektu skryptu:
/**
* Formats top row of sheet using our header row style.
*/
function formatRowHeader() {
// Get the current active sheet and the top row's range.
var sheet = SpreadsheetApp.getActiveSheet();
var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
// Apply each format to the top row: bold white text,
// blue-green background, and a solid black border
// around the cells.
headerRange
.setFontWeight('bold')
.setFontColor('#ffffff')
.setBackground('#007272')
.setBorder(
true, true, true, true, null, null,
null,
SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
}
- Zapisz projekt skryptu.
Weryfikacja kodu
Podobnie jak w przypadku wielu zadań formatowania, kod Apps Script do jego wdrożenia jest prosty. W pierwszych 2 wierszach użyto znanych Ci już metod, aby uzyskać odwołanie do bieżącego aktywnego arkusza (sheet) i górnego wiersza arkusza (headerRange)). Metoda Sheet.getRange(row, column, numRows, numColumns) określa górny wiersz, uwzględniając tylko te kolumny, które zawierają dane. Metoda Sheet.getLastColumn() zwraca indeks kolumny ostatniej kolumny, która zawiera dane w arkuszu. W naszym przykładzie jest to kolumna E (url).
Pozostała część kodu wywołuje różne metody Range, aby zastosować wybrane formatowanie do wszystkich komórek w headerRange. Aby kod był czytelny, używamy łączenia metod, dzięki czemu każda metoda formatowania jest wywoływana jedna po drugiej:
Range.setFontWeight(fontWeight)służy do ustawiania pogrubionej czcionki.Range.setFontColor(color)służy do ustawiania koloru czcionki na biały.Range.setBackground(color)służy do ustawiania koloru tła na ciemny niebieskozielony.setBorder(top, left, bottom, right, vertical, horizontal, color, style)umieszcza wokół komórek zakresu jednolite czarne obramowanie.
Ostatnia metoda ma kilka parametrów, więc przyjrzyjmy się, do czego służy każdy z nich. Pierwsze 4 parametry (wszystkie ustawione na true) informują Apps Script, że obramowanie ma zostać dodane powyżej, poniżej oraz po lewej i prawej stronie zakresu. Piąty i szósty parametr (null i null) informują Apps Script, aby nie zmieniać żadnych linii obramowania w wybranym zakresie. Siódmy parametr (null) wskazuje, że kolor obramowania powinien być domyślnie czarny. Ostatni parametr określa typ stylu obramowania, który ma być używany. Jest on wybierany z opcji podanych przez SpreadsheetApp.BorderStyle.
Wyniki
Aby zobaczyć, jak działa funkcja formatowania:
- Jeśli jeszcze tego nie zrobisz, zapisz projekt skryptu w edytorze Apps Script.
- Kliknij kolejno Szybkie formaty > Formatuj nagłówek wiersza.
Wyniki powinny wyglądać tak:
Zautomatyzowano zadanie formatowania. W następnej sekcji zastosujemy tę samą technikę, aby utworzyć inny styl formatowania nagłówków kolumn.
5. Formatowanie nagłówka kolumny
Jeśli możesz utworzyć spersonalizowany nagłówek wiersza, możesz też utworzyć nagłówek kolumny. Nagłówki kolumn zwiększają czytelność niektórych zbiorów danych. Na przykład kolumnę titles w tym arkuszu kalkulacyjnym można ulepszyć za pomocą tych opcji formatowania:
- Pogrubianie tekstu
- Stosowanie kursywy
- Dodawanie obramowań komórek
- wstawianie hiperlinków przy użyciu zawartości kolumny url; Po dodaniu tych hiperlinków możesz usunąć kolumnę url, aby uporządkować arkusz.
Następnie zaimplementujesz funkcję formatColumnHeader(), aby zastosować te zmiany w pierwszej kolumnie arkusza. Aby ułatwić czytanie kodu, zaimplementujesz też 2 funkcje pomocnicze.
Implementacja
Podobnie jak wcześniej musisz dodać funkcję, która zautomatyzuje formatowanie nagłówka kolumny. Wykonaj te czynności:
- W edytorze Apps Script dodaj na końcu projektu skryptu tę funkcję
formatColumnHeader():
/**
* Formats the column header of the active sheet.
*/
function formatColumnHeader() {
var sheet = SpreadsheetApp.getActiveSheet();
// Get total number of rows in data range, not including
// the header row.
var numRows = sheet.getDataRange().getLastRow() - 1;
// Get the range of the column header.
var columnHeaderRange = sheet.getRange(2, 1, numRows, 1);
// Apply text formatting and add borders.
columnHeaderRange
.setFontWeight('bold')
.setFontStyle('italic')
.setBorder(
true, true, true, true, null, null,
null,
SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
// Call helper method to hyperlink the first column contents
// to the url column contents.
hyperlinkColumnHeaders_(columnHeaderRange, numRows);
}
- Dodaj te funkcje pomocnicze na końcu projektu skryptu, po funkcji
formatColumnHeader():
/**
* Helper function that hyperlinks the column header with the
* 'url' column contents. The function then removes the column.
*
* @param {object} headerRange The range of the column header
* to update.
* @param {number} numRows The size of the column header.
*/
function hyperlinkColumnHeaders_(headerRange, numRows) {
// Get header and url column indices.
var headerColIndex = 1;
var urlColIndex = columnIndexOf_('url');
// Exit if the url column is missing.
if(urlColIndex == -1)
return;
// Get header and url cell values.
var urlRange =
headerRange.offset(0, urlColIndex - headerColIndex);
var headerValues = headerRange.getValues();
var urlValues = urlRange.getValues();
// Updates header values to the hyperlinked header values.
for(var row = 0; row < numRows; row++){
headerValues[row][0] = '=HYPERLINK("' + urlValues[row]
+ '","' + headerValues[row] + '")';
}
headerRange.setValues(headerValues);
// Delete the url column to clean up the sheet.
SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex);
}
/**
* Helper function that goes through the headers of all columns
* and returns the index of the column with the specified name
* in row 1. If a column with that name does not exist,
* this function returns -1. If multiple columns have the same
* name in row 1, the index of the first one discovered is
* returned.
*
* @param {string} colName The name to find in the column
* headers.
* @return The index of that column in the active sheet,
* or -1 if the name isn't found.
*/
function columnIndexOf_(colName) {
// Get the current column names.
var sheet = SpreadsheetApp.getActiveSheet();
var columnHeaders =
sheet.getRange(1, 1, 1, sheet.getLastColumn());
var columnNames = columnHeaders.getValues();
// Loops through every column and returns the column index
// if the row 1 value of that column matches colName.
for(var col = 1; col <= columnNames[0].length; col++)
{
if(columnNames[0][col-1] === colName)
return col;
}
// Returns -1 if a column named colName does not exist.
return -1;
}
- Zapisz projekt skryptu.
Weryfikacja kodu
Przyjrzyjmy się kodowi w każdej z tych 3 funkcji osobno:
formatColumnHeader()
Jak zapewne się domyślasz, w pierwszych wierszach tej funkcji ustawiane są zmienne, które odwołują się do arkusza i zakresu, które nas interesują:
- Aktywny arkusz jest przechowywany w
sheet. - Liczba wierszy w nagłówku kolumny jest obliczana i zapisywana w zmiennej
numRows. W tym przypadku kod odejmuje 1, aby liczba wierszy nie obejmowała nagłówka kolumny:title. - Zakres obejmujący nagłówek kolumny jest przechowywany w elemencie
columnHeaderRange.
Kod stosuje następnie obramowania i pogrubienie do zakresu nagłówków kolumn, tak jak w formatRowHeader(). W tym przypadku znak Range.setFontStyle(fontStyle) jest też używany do pisania tekstu kursywą.
Dodanie hiperlinków do kolumny nagłówka jest bardziej złożone, więc formatColumnHeader() wywołuje hyperlinkColumnHeaders_(headerRange, numRows), aby wykonać to zadanie. Dzięki temu kod jest przejrzysty i czytelny.
hyperlinkColumnHeaders_(headerRange, numRows)
Ta funkcja pomocnicza najpierw identyfikuje indeksy kolumn nagłówka (przyjmuje się, że jest to indeks 1) i kolumny url. Wywołuje funkcję columnIndexOf_('url'), aby uzyskać indeks kolumny adresu URL. Jeśli kolumna url nie zostanie znaleziona, metoda zakończy działanie bez modyfikowania danych.
Funkcja pobiera nowy zakres (urlRange), który obejmuje adresy URL odpowiadające wierszom kolumny nagłówka. Odbywa się to za pomocą metody Range.offset(rowOffset, columnOffset), która gwarantuje, że oba zakresy będą miały ten sam rozmiar. Następnie pobierane są wartości z kolumn headerColumn i url (headerValues i urlValues).
Następnie funkcja przechodzi przez każdą komórkę nagłówka kolumny i zastępuje ją =HYPERLINK() formułą Arkuszy utworzoną na podstawie nagłówka i url zawartości kolumny. Zmodyfikowane wartości nagłówka są następnie wstawiane do arkusza za pomocą funkcji Range.setValues(values).
Na koniec, aby zachować przejrzystość arkusza i wyeliminować zbędne informacje, wywoływana jest funkcja Sheet.deleteColumn(columnPosition), która usuwa kolumnę url.
columnIndexOf_(colName)
Ta funkcja pomocnicza to prosta funkcja narzędziowa, która wyszukuje w pierwszym wierszu arkusza konkretną nazwę. W pierwszych 3 wierszach używane są znane Ci już metody pobierania listy nazw nagłówków kolumn z wiersza 1 arkusza kalkulacyjnego. Te nazwy są przechowywane w zmiennej columnNames.
Następnie funkcja sprawdza każdą nazwę po kolei. Jeśli znajdzie kolumnę o nazwie zgodnej z wyszukiwaną, zatrzyma się i zwróci jej indeks. Jeśli po przejrzeniu całej listy nie znajdzie nazwy, zwraca wartość -1, aby zasygnalizować, że nazwy nie znaleziono.
Wyniki
Aby zobaczyć, jak działa funkcja formatowania:
- Jeśli jeszcze tego nie zrobisz, zapisz projekt skryptu w edytorze Apps Script.
- Kliknij Szybkie formaty > Formatuj nagłówek kolumny.
Wyniki powinny wyglądać tak:
Kolejne zadanie formatowania zostało zautomatyzowane. Po sformatowaniu nagłówków kolumn i wierszy w następnej sekcji dowiesz się, jak sformatować dane.
6. Formatowanie zbioru danych
Teraz, gdy masz już nagłówki, utwórzmy funkcję, która sformatuje pozostałe dane w arkuszu. Użyjemy tych opcji formatowania:
- Naprzemienne kolory tła wierszy (tzw. paskowanie)
- Zmiana formatów daty
- Stosowanie obramowań
- Automatyczne dopasowywanie rozmiaru wszystkich kolumn i wierszy
Teraz utworzysz funkcję formatDataset() i dodatkową metodę pomocniczą, aby zastosować te formaty do danych arkusza.
Implementacja
Podobnie jak wcześniej dodaj funkcję, która zautomatyzuje formatowanie danych. Wykonaj te czynności:
- W edytorze Apps Script dodaj na końcu projektu skryptu tę funkcję
formatDataset():
/**
* Formats the sheet data, excluding the header row and column.
* Applies the border and banding, formats the 'release_date'
* column, and autosizes the columns and rows.
*/
function formatDataset() {
// Get the active sheet and data range.
var sheet = SpreadsheetApp.getActiveSheet();
var fullDataRange = sheet.getDataRange();
// Apply row banding to the data, excluding the header
// row and column. Only apply the banding if the range
// doesn't already have banding set.
var noHeadersRange = fullDataRange.offset(
1, 1,
fullDataRange.getNumRows() - 1,
fullDataRange.getNumColumns() - 1);
if (! noHeadersRange.getBandings()[0]) {
// The range doesn't already have banding, so it's
// safe to apply it.
noHeadersRange.applyRowBanding(
SpreadsheetApp.BandingTheme.LIGHT_GREY,
false, false);
}
// Call a helper function to apply date formatting
// to the column labeled 'release_date'.
formatDates_( columnIndexOf_('release_date') );
// Set a border around all the data, and resize the
// columns and rows to fit.
fullDataRange.setBorder(
true, true, true, true, null, null,
null,
SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
sheet.autoResizeColumns(1, fullDataRange.getNumColumns());
sheet.autoResizeRows(1, fullDataRange.getNumRows());
}
- Na końcu projektu skryptu, po funkcji
formatDataset(), dodaj tę funkcję pomocniczą:
/**
* Helper method that applies a
* "Month Day, Year (Day of Week)" date format to the
* indicated column in the active sheet.
*
* @param {number} colIndex The index of the column
* to format.
*/
function formatDates_(colIndex) {
// Exit if the given column index is -1, indicating
// the column to format isn't present in the sheet.
if (colIndex < 0)
return;
// Set the date format for the date column, excluding
// the header row.
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)
.setNumberFormat("mmmm dd, yyyy (dddd)");
}
- Zapisz projekt skryptu.
Weryfikacja kodu
Przyjrzyjmy się kodowi w każdej z tych 2 funkcji osobno:
formatDataset()
Ta funkcja działa podobnie do poprzednich funkcji formatowania, które zostały już przez Ciebie wdrożone. Najpierw pobiera zmienne, które będą przechowywać odwołania do aktywnego arkusza (sheet) i zakresu danych (fullDataRange).
Po drugie, używa metody Range.offset(rowOffset, columnOffset, numRows, numColumns) do utworzenia zakresu (noHeadersRange), który obejmuje wszystkie dane w arkuszu z wyłączeniem nagłówków kolumn i wierszy. Następnie kod sprawdza, czy w nowym zakresie istnieje już formatowanie warunkowe (za pomocą funkcji Range.getBandings()). Jest to konieczne, ponieważ Apps Script zgłasza błąd, jeśli spróbujesz zastosować nowe formatowanie warunkowe w miejscu, w którym już ono istnieje. Jeśli pasmo nie istnieje, funkcja dodaje jasnoszare pasmo za pomocą funkcji Range.applyRowBanding(bandingTheme, showHeader, showFooter). W przeciwnym razie funkcja przechodzi dalej.
W następnym kroku wywoływana jest funkcja pomocnicza formatDates_(colIndex), która formatuje daty w kolumnie oznaczonej jako „release_date” (opisanej poniżej). Kolumna jest określana za pomocą funkcji pomocniczej columnIndexOf_(colName), którą zaimplementowano wcześniej.
Na koniec formatowanie jest uzupełniane przez dodanie kolejnego obramowania (jak wcześniej) i automatyczne dopasowanie rozmiaru każdej kolumny i każdego wiersza do zawartych w nich danych za pomocą metod Sheet.autoResizeColumns(columnPosition) i Sheet.autoResizeColumns(columnPosition).
formatDates_(colIndex)
Ta funkcja pomocnicza stosuje określony format daty do kolumny przy użyciu podanego indeksu kolumny. W szczególności formatuje wartości daty jako „Miesiąc Dzień, Rok (Dzień tygodnia)”.
Najpierw funkcja sprawdza, czy podany indeks kolumny jest prawidłowy (czyli większy lub równy 0). W przeciwnym razie funkcja zwraca wartość bez wykonywania żadnych działań. To sprawdzenie zapobiega błędom, które mogą wystąpić, jeśli np. arkusz nie zawiera kolumny „release_date”.
Po sprawdzeniu indeksu kolumny funkcja pobiera zakres obejmujący tę kolumnę (z wyłączeniem wiersza nagłówka) i używa funkcji Range.setNumberFormat(numberFormat) do zastosowania formatowania.
Wyniki
Aby zobaczyć, jak działa funkcja formatowania:
- Jeśli jeszcze tego nie zrobisz, zapisz projekt skryptu w edytorze Apps Script.
- Kliknij Szybkie formaty > Formatuj zbiór danych.
Wyniki powinny wyglądać tak:
Kolejne zadanie formatowania zostało zautomatyzowane. Teraz, gdy masz już dostępne te polecenia formatowania, dodajmy więcej danych, do których można je zastosować.
7. Pobieranie i formatowanie danych interfejsu API
W tym laboratorium kodowania pokazaliśmy, jak używać Apps Script jako alternatywnego sposobu formatowania arkusza kalkulacyjnego. Następnie napiszesz kod, który pobiera dane z publicznego interfejsu API, wstawia je do arkusza kalkulacyjnego i formatuje w taki sposób, aby były czytelne.
W ostatnim kursie dowiedzieliśmy się, jak pobierać dane z interfejsu API. W tym przypadku zastosujesz te same techniki. W tym ćwiczeniu użyjemy publicznego interfejsu Star Wars API (SWAPI), aby wypełnić arkusz kalkulacyjny. W tym celu użyjesz interfejsu API, aby uzyskać informacje o głównych postaciach z pierwszych trzech filmów z serii Gwiezdne Wojny.
Kod wywoła interfejs API, aby pobrać dużą ilość danych w formacie JSON, przeanalizować odpowiedź, umieścić dane w nowym arkuszu, a następnie sformatować arkusz.
Implementacja
W tej sekcji dodasz kilka dodatkowych pozycji menu. Każda pozycja menu wywołuje skrypt opakowujący, który przekazuje zmienne specyficzne dla pozycji do funkcji głównej (createResourceSheet_()). Zaimplementujesz tę funkcję i 3 dodatkowe funkcje pomocnicze. Podobnie jak wcześniej, funkcje pomocnicze pomagają wyodrębnić logicznie podzielone części zadania i utrzymać czytelność kodu.
Wykonaj te czynności:
- W edytorze Apps Script zaktualizuj funkcję
onOpen()w projekcie skryptu, aby pasowała do tego kodu:
/**
* A special function that runs when the spreadsheet is opened
* or reloaded, used to add a custom menu to the spreadsheet.
*/
function onOpen() {
// Get the Ui object.
var ui = SpreadsheetApp.getUi();
// Create and add a named menu and its items to the menu bar.
ui.createMenu('Quick formats')
.addItem('Format row header', 'formatRowHeader')
.addItem('Format column header', 'formatColumnHeader')
.addItem('Format dataset', 'formatDataset')
.addSeparator()
.addSubMenu(ui.createMenu('Create character sheet')
.addItem('Episode IV', 'createPeopleSheetIV')
.addItem('Episode V', 'createPeopleSheetV')
.addItem('Episode VI', 'createPeopleSheetVI')
)
.addToUi();
}
- Zapisz projekt skryptu.
- W edytorze skryptów wybierz
onOpenz listy funkcji i kliknij Uruchom. Spowoduje to uruchomienie funkcjionOpen(), która ponownie utworzy menu arkusza kalkulacyjnego z dodanymi przez Ciebie nowymi opcjami. - Aby utworzyć plik Apps Script, obok pozycji Pliki kliknij Dodaj plik
> Skrypt. - Nazwij nowy skrypt „API” i naciśnij Enter. (Apps Script automatycznie dodaje do nazwy pliku skryptu rozszerzenie
.gs). - Zastąp kod w nowym pliku API.gs tym kodem:
/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode IV.
*/
function createPeopleSheetIV() {
createResourceSheet_('characters', 1, "IV");
}
/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode V.
*/
function createPeopleSheetV() {
createResourceSheet_('characters', 2, "V");
}
/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode VI.
*/
function createPeopleSheetVI() {
createResourceSheet_('characters', 3, "VI");
}
/**
* Creates a formatted sheet filled with user-specified
* information from the Star Wars API. If the sheet with
* this data exists, the sheet is overwritten with the API
* information.
*
* @param {string} resourceType The type of resource.
* @param {number} idNumber The identification number of the film.
* @param {number} episodeNumber The Star Wars film episode number.
* This is only used in the sheet name.
*/
function createResourceSheet_(
resourceType, idNumber, episodeNumber) {
// Fetch the basic film data from the API.
var filmData = fetchApiResourceObject_(
"https://swapi.dev/api/films/" + idNumber);
// Extract the API URLs for each resource so the code can
// call the API to get more data about each individually.
var resourceUrls = filmData[resourceType];
// Fetch each resource from the API individually and push
// them into a new object list.
var resourceDataList = [];
for(var i = 0; i < resourceUrls.length; i++){
resourceDataList.push(
fetchApiResourceObject_(resourceUrls[i])
);
}
// Get the keys used to reference each part of data within
// the resources. The keys are assumed to be identical for
// each object since they're all the same resource type.
var resourceObjectKeys = Object.keys(resourceDataList[0]);
// Create the sheet with the appropriate name. It
// automatically becomes the active sheet when it's created.
var resourceSheet = createNewSheet_(
"Episode " + episodeNumber + " " + resourceType);
// Add the API data to the new sheet, using each object
// key as a column header.
fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);
// Format the new sheet using the same styles the
// 'Quick Formats' menu items apply. These methods all
// act on the active sheet, which is the one just created.
formatRowHeader();
formatColumnHeader();
formatDataset();
}
- Na końcu pliku projektu skryptu API.gs dodaj te funkcje pomocnicze:
/**
* Helper function that retrieves a JSON object containing a
* response from a public API.
*
* @param {string} url The URL of the API object being fetched.
* @return {object} resourceObject The JSON object fetched
* from the URL request to the API.
*/
function fetchApiResourceObject_(url) {
// Make request to API and get response.
var response =
UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
// Parse and return the response as a JSON object.
var json = response.getContentText();
var responseObject = JSON.parse(json);
return responseObject;
}
/**
* Helper function that creates a sheet or returns an existing
* sheet with the same name.
*
* @param {string} name The name of the sheet.
* @return {object} The created or existing sheet
* of the same name. This sheet becomes active.
*/
function createNewSheet_(name) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Returns an existing sheet if it has the specified
// name. Activates the sheet before returning.
var sheet = ss.getSheetByName(name);
if (sheet) {
return sheet.activate();
}
// Otherwise it makes a sheet, set its name, and returns it.
// New sheets created this way automatically become the active
// sheet.
sheet = ss.insertSheet(name);
return sheet;
}
/**
* Helper function that adds API data to the sheet.
* Each object key is used as a column header in the new sheet.
*
* @param {object} resourceSheet The sheet object being modified.
* @param {object} objectKeys The list of keys for the resources.
* @param {object} resourceDataList The list of API
* resource objects containing data to add to the sheet.
*/
function fillSheetWithData_(
resourceSheet, objectKeys, resourceDataList) {
// Set the dimensions of the data range being added to the sheet.
var numRows = resourceDataList.length;
var numColumns = objectKeys.length;
// Get the resource range and associated values array. Add an
// extra row for the column headers.
var resourceRange =
resourceSheet.getRange(1, 1, numRows + 1, numColumns);
var resourceValues = resourceRange.getValues();
// Loop over each key value and resource, extracting data to
// place in the 2D resourceValues array.
for (var column = 0; column < numColumns; column++) {
// Set the column header.
var columnHeader = objectKeys[column];
resourceValues[0][column] = columnHeader;
// Read and set each row in this column.
for (var row = 1; row < numRows + 1; row++) {
var resource = resourceDataList[row - 1];
var value = resource[columnHeader];
resourceValues[row][column] = value;
}
}
// Remove any existing data in the sheet and set the new values.
resourceSheet.clear()
resourceRange.setValues(resourceValues);
}
- Zapisz projekt skryptu.
Weryfikacja kodu
Właśnie dodano dużo kodu. Przyjrzyjmy się poszczególnym funkcjom, aby zrozumieć, jak działają:
onOpen()
W tym miejscu dodano kilka pozycji do menu Quick formats. Ustawiono linię separatora, a następnie użyto metody Menu.addSubMenu(menu) do utworzenia zagnieżdżonej struktury menu z 3 nowymi elementami. Nowe elementy są dodawane za pomocą metody Menu.addItem(caption, functionName).
Funkcje otoki
Dodane elementy menu robią coś podobnego: próbują utworzyć arkusz z danymi pobranymi z SWAPI. Różnią się one tylko tym, że każda z nich skupia się na innym filmie.
Wygodnie byłoby napisać jedną funkcję do tworzenia arkusza, która akceptuje parametr określający, jakiego filmu użyć. Metoda Menu.addItem(caption, functionName) nie pozwala jednak przekazywać do niej parametrów, gdy jest wywoływana przez menu. Jak więc uniknąć pisania tego samego kodu 3 razy?
Odpowiedź to funkcje opakowujące. Są to proste funkcje, które możesz wywołać, aby natychmiast wywołać inną funkcję z określonymi parametrami.
W tym przypadku kod używa 3 funkcji opakowujących: createPeopleSheetIV(), createPeopleSheetV() i createPeopleSheetVI(). Elementy menu są połączone z tymi funkcjami. Gdy klikniesz pozycję menu, funkcja opakowująca zostanie wykonana i natychmiast wywoła główną funkcję tworzenia arkusza createResourceSheet_(resourceType, idNumber, episodeNumber), przekazując parametry odpowiednie dla danej pozycji menu. W tym przypadku oznacza to poproszenie funkcji tworzenia arkusza o utworzenie arkusza wypełnionego danymi głównych bohaterów jednego z filmów z serii Gwiezdne Wojny.
createResourceSheet_(resourceType, idNumber, episodeNumber)
To główna funkcja narzędzia do tworzenia arkuszy w tym ćwiczeniu. Przy pomocy funkcji pomocniczych pobiera dane z interfejsu API, analizuje je, tworzy arkusz, zapisuje w nim dane z interfejsu API, a następnie formatuje arkusz za pomocą funkcji utworzonych w poprzednich sekcjach. Przyjrzyjmy się szczegółom:
Najpierw funkcja używa fetchApiResourceObject_(url), aby wysłać do interfejsu API żądanie pobrania podstawowych informacji o filmie. Odpowiedź interfejsu API zawiera zbiór adresów URL, których kod może używać do uzyskiwania szczegółowych informacji o konkretnych osobach (zwanych tu zasobami) z filmów. Kod zbiera wszystkie te informacje w tablicy resourceUrls.
Następnie kod wielokrotnie używa funkcji fetchApiResourceObject_(url), aby wywołać interfejs API dla każdego adresu URL zasobu w resourceUrls. Wyniki są przechowywane w tablicy resourceDataList. Każdy element tej tablicy to obiekt opisujący inną postać z filmu.
Obiekty danych zasobów mają kilka wspólnych kluczy, które są powiązane z informacjami o danej postaci. Na przykład klucz „name' jest mapowany na imię postaci w filmie. Zakładamy, że klucze każdego obiektu danych zasobu są identyczne, ponieważ mają one używać wspólnych struktur obiektów. Lista kluczy będzie potrzebna później, więc kod zapisuje ją w resourceObjectKeys za pomocą metody JavaScript Object.keys().
Następnie funkcja budująca wywołuje funkcję pomocniczą createNewSheet_(name), aby utworzyć arkusz, w którym zostaną umieszczone nowe dane. Wywołanie tej funkcji pomocniczej powoduje też aktywowanie nowego arkusza.
Po utworzeniu arkusza wywoływana jest funkcja pomocnicza fillSheetWithData_(resourceSheet, objectKeys, resourceDataList), która dodaje do arkusza wszystkie dane z interfejsu API.
Na koniec wywoływane są wszystkie utworzone wcześniej funkcje formatowania, aby zastosować te same reguły formatowania do nowych danych. Nowy arkusz jest aktywny, więc kod może ponownie użyć tych funkcji bez modyfikacji.
fetchApiResourceObject_(url)
Ta funkcja pomocnicza jest podobna do funkcji pomocniczej fetchBookData_(ISBN) użytej w poprzednim samouczku Praca z danymi. Pobiera podany adres URL i używa metody UrlFetchApp.fetch(url, params), aby uzyskać odpowiedź. Odpowiedź jest następnie analizowana w obiekt JSON za pomocą metod HTTPResponse.getContextText() i JSON.parse(json) JavaScript. Zwracany jest wynikowy obiekt JSON.
createNewSheet_(name)
Ta funkcja pomocnicza jest dość prosta. Najpierw sprawdza, czy w arkuszu kalkulacyjnym istnieje arkusz o podanej nazwie. Jeśli tak, funkcja aktywuje arkusz i zwraca go.
Jeśli arkusz nie istnieje, funkcja tworzy go z wartością Spreadsheet.insertSheet(sheetName), aktywuje go i zwraca nowy arkusz.
fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)
Ta funkcja pomocnicza odpowiada za wypełnianie nowego arkusza danymi z interfejsu API. Przyjmuje jako parametry nowy arkusz, listę kluczy obiektów i listę obiektów zasobów interfejsu API. Każdy klucz obiektu reprezentuje kolumnę w nowym arkuszu, a każdy obiekt zasobu – wiersz.
Najpierw funkcja oblicza liczbę wierszy i kolumn potrzebnych do przedstawienia nowych danych interfejsu API. Jest to odpowiednio rozmiar listy zasobów i kluczy. Następnie funkcja określa zakres wyjściowy (resourceRange), w którym zostaną umieszczone dane, i dodaje dodatkowy wiersz na nagłówki kolumn. Zmienna resourceValues zawiera dwuwymiarową tablicę wartości wyodrębnioną z resourceRange.
Funkcja przechodzi następnie przez każdy klucz obiektu na liście objectKeys. Klucz jest ustawiany jako nagłówek kolumny, a następnie druga pętla przechodzi przez każdy obiekt zasobu. W przypadku każdej pary (wiersz, kolumna) odpowiednie informacje o interfejsie API są kopiowane do elementu resourceValues[row][column].
Po wypełnieniu komórki resourceValues arkusz docelowy jest czyszczony za pomocą funkcji Sheet.clear(), jeśli zawiera dane z poprzednich kliknięć elementów menu. Na koniec nowe wartości są zapisywane w arkuszu.
Wyniki
Wyniki swojej pracy możesz sprawdzić w ten sposób:
- Jeśli jeszcze tego nie zrobisz, zapisz projekt skryptu w edytorze Apps Script.
- Kliknij kolejno Szybkie formaty > Utwórz arkusz postaci > Odcinek IV.
Wyniki powinny wyglądać tak:
Kod do importowania danych do Arkuszy i automatycznego formatowania został już napisany.
8. Podsumowanie
Gratulujemy ukończenia tego ćwiczenia. Poznaliśmy już niektóre opcje formatowania Arkuszy, które można uwzględnić w projektach Apps Script, i stworzyliśmy imponującą aplikację, która importuje i formatuje duży zbiór danych z interfejsu API.
Czy ten codelab był przydatny?
Czego się dowiedziałeś
- Jak stosować różne operacje formatowania Arkuszy za pomocą Apps Script.
- Jak tworzyć podmenu za pomocą funkcji
onOpen(). - Jak sformatować pobraną listę obiektów JSON w nowym arkuszu danych za pomocą Apps Script.
Co dalej?
W kolejnym module z tej playlisty dowiesz się, jak używać Apps Script do wizualizacji danych na wykresie i eksportowania wykresów do prezentacji Google.
Następny codelab znajdziesz na stronie Tworzenie wykresów i prezentowanie danych w Prezentacjach.