Podstawy Apps Script w Arkuszach Google nr 4: formatowanie danych

1. Wstęp

Witamy w czwartej części playlisty Podstawy Apps Script z ćwiczeniami z Arkuszy Google.

Wykonując te ćwiczenia z programowania, dowiesz się, jak formatować dane arkuszy kalkulacyjnych w Apps Script i pisać funkcje, by tworzyć uporządkowane arkusze zawierające sformatowane dane pobrane z publicznego interfejsu API.

Czego się nauczysz

  • Stosowanie różnych operacji formatowania w Arkuszach Google w języku 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 4. ćwiczenie z programowania z playlisty Podstawy Apps Script dotyczącej Arkuszy Google. Zanim rozpoczniesz te ćwiczenia z programowania, wykonaj te czynności:

  1. Makra i funkcje niestandardowe
  2. Arkusze kalkulacyjne, arkusze kalkulacyjne i zakresy
  3. Praca z danymi

Czego potrzebujesz

  • Omówienie podstawowych tematów Apps Script omówionych w poprzednich ćwiczeniach z tej playlisty.
  • Podstawowe informacje o edytorze Apps Script
  • Podstawowe informacje o Arkuszach Google
  • Możliwość czytania Arkuszy notacja A1
  • Podstawowa znajomość JavaScriptu i jego klasy String

2. Skonfiguruj

Aby kontynuować, potrzebujesz arkusza kalkulacyjnego z danymi. Tak jak wcześniej udostępniliśmy arkusz danych, który możesz skopiować do tych ćwiczeń. Wykonaj te czynności:

  1. Kliknij ten link, aby skopiować arkusz danych, a następnie kliknij Utwórz kopię. Nowy arkusz kalkulacyjny zostanie umieszczony w folderze Dysku Google i zostanie zatytułowany „"Copy of Data Formatowanie&quot”.
  2. Kliknij tytuł arkusza kalkulacyjnego i wybierz „"Kopia formatowania danych”. Twój arkusz powinien wyglądać następująco: z kilkoma podstawowymi informacjami o pierwszych 3 filmach o Gwiezdnych wojnach:

C4F49788ed82502b.png

  1. Wybierz Rozszerzenia > Apps Script, aby otworzyć edytor skryptów.
  2. Kliknij tytuł projektu Apps Script i zmień go na &"Projekt bez tytułu&"Formatowanie danych." Kliknij Zmień nazwę, by zapisać zmianę nazwy.

Dzięki temu arkuszowi projektowemu możesz rozpocząć ćwiczenia z programowania. Aby dowiedzieć się więcej o podstawowym formatowaniu w Apps Script, przejdź do następnej sekcji.

3. Utwórz menu niestandardowe

W Arkuszach Google możesz zastosować kilka podstawowych metod formatowania. W ćwiczeniach poniżej przedstawiliśmy kilka sposobów formatowania danych. Aby ułatwić Ci kontrolowanie działań związanych z formatowaniem, utwórz niestandardowe menu z elementami, których będziesz potrzebować. Proces tworzenia niestandardowych menu został opisany w ćwiczeniach z kursu Praca z danymi, ale szybko je tu wyjaśnimy.

Wdrażanie

Utwórzmy niestandardowe menu.

  1. W edytorze Apps Script zastąp kod w projekcie skryptu tym fragmentem:
/**
 * 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();
}
  1. Zapisz projekt skryptu.
  2. W edytorze skryptów wybierz onOpen z listy funkcji i kliknij Uruchom. Powoduje to uruchomienie onOpen() w celu ponownego utworzenia menu arkusza kalkulacyjnego, więc nie musisz ponownie wczytywać arkusza.

Weryfikacja kodu

Sprawdźmy, jak działa ten kod. W onOpen() pierwszy wiersz korzysta z metody getUi(), by pobrać obiekt Ui reprezentujący interfejs aktywnego arkusza kalkulacyjnego, z którym jest powiązany ten skrypt.

W kolejnych wierszach tworzysz menu (Quick formats), dodajesz pozycje menu (Format row header, Format column header i Format dataset), a następnie dodajesz 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 uruchomiona po wybraniu elementu menu. Na przykład wybranie pozycji menu Format row header powoduje, że Arkusze próbują uruchomić funkcję formatRowHeader() (która jeszcze nie istnieje).

Wyniki

W arkuszu kalkulacyjnym kliknij menu Quick formats, aby wyświetlić nowe pozycje menu:

1d639a41f3104864.png

Kliknięcie tych elementów powoduje wystąpienie błędu, ponieważ nie zostały zaimplementowane odpowiednie funkcje, więc przejdźmy dalej.

4. Formatowanie wiersza nagłówka

Zbiory danych w arkuszach kalkulacyjnych często mają wiersze nagłówka identyfikujące dane w każdej kolumnie. Dobrze jest sformatować wiersze nagłówka, aby oddzielić je od innych danych w arkuszu kalkulacyjnym.

W pierwszym ćwiczeniu z programowania udało Ci się utworzyć makro do nagłówka i dostosować jego kod. Tutaj sformatujesz wiersz nagłówka od zera za pomocą Apps Script. Wiersz nagłówka, który utworzysz, pogrubi tekst nagłówka, zmieni kolor tła na ciemnoniebieski, zielony, tekst będzie biały i dodamy pewne jednolite obramowanie.

Wdrażanie

Aby wdrożyć formatowanie, użyjesz tych samych metod usługi arkuszy kalkulacyjnych co wcześniej, ale teraz użyjesz niektórych metod formatowania. Wykonaj te czynności:

  1. W edytorze Apps Script dodaj na końcu projektu skryptu następującą funkcję:
/**
 * 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);

}
  1. Zapisz projekt skryptu.

Weryfikacja kodu

Tak jak w przypadku wielu zadań formatowania, kod Apps Script jest prosty w implementacji. W 2 pierwszych wierszach używane są metody, które znasz wcześniej, aby uzyskać odniesienie do bieżącego aktywnego arkusza (sheet) i górnego wiersza arkusza (headerRange)). Metoda Sheet.getRange(row, column, numRows, numColumns) wskazuje najwyższy wiersz, w tym tylko kolumny z danymi. Metoda Sheet.getLastColumn() zwraca indeks ostatniej kolumny zawierającej dane w arkuszu. W naszym przykładzie jest to kolumna E (url).

Reszta kodu po prostu wywołuje różne metody Range, by zastosować wybrane opcje formatowania do wszystkich komórek w headerRange. Aby ułatwić czytanie kodu, korzystamy z łańcuchów metod, które kolejno wywołują poszczególne metody formatowania:

Ostatnia metoda ma kilka parametrów, sprawdźmy więc, co daje każdy z nich. Pierwsze cztery parametry tutaj (wszystkie ustawione na true) wskazują Apps Script, że należy dodać obramowanie powyżej, poniżej oraz z lewej i prawej strony zakresu. Piąty i szósty parametr (null i null) wskazuje skrypt Apps Script, by 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, korzystając z opcji dostępnych w SpreadsheetApp.BorderStyle.

Wyniki

Aby sprawdzić działanie funkcji formatowania, wykonaj te czynności:

  1. Zapisz projekt skryptu w edytorze Apps Script, jeśli jeszcze go nie masz.
  2. Kliknij pozycję menu Szybkie formaty > Formatuj nagłówek wiersza.

Powinny one wyglądać tak:

a1a63770c2c3becc.gif

Zadanie formatowania jest teraz automatyczne. W następnej sekcji stosujesz tę samą technikę do tworzenia różnych stylów nagłówków dla 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żesz uzupełnić, wybierając jeden z tych formatów:

  • Pogrubienie tekstu
  • Kursywa
  • Dodawanie obramowań komórek
  • Wstawianie hiperlinków przy użyciu zawartości kolumny url. Po dodaniu hiperlinków możesz usunąć kolumnę url, by uporządkować arkusz.

Następnie wdrożysz funkcję formatColumnHeader(), aby zastosować te zmiany w pierwszej kolumnie arkusza. Aby ułatwić czytanie kodu, wdrożysz też 2 funkcje pomocnicze.

Wdrażanie

Aby zautomatyzować formatowanie nagłówka kolumny, musisz dodać funkcję. Wykonaj te czynności:

  1. W edytorze Apps Script dodaj na końcu projektu skryptu następującą 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); 
}
  1. Po zakończeniu funkcji formatColumnHeader() dodaj na końcu projektu skryptu następujące funkcje pomocnicze:
/**
 * 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; 
}
  1. Zapisz projekt skryptu.

Weryfikacja kodu

Przeanalizujmy kod w każdej z tych trzech funkcji:

formatColumnHeader()

Jak się spodziewasz, pierwsze kilka wierszy tej funkcji będzie zawierać zmienne odwołujące się do arkusza i zakresu, których interesują:

  • Aktywny arkusz jest przechowywany w sheet.
  • Liczba wierszy w nagłówku kolumny jest obliczana i zapisywana w tabeli numRows. Tutaj kod jest odejmowany jeden, więc liczba wierszy nie zawiera nagłówka kolumny: title.
  • Zakres obejmujący nagłówek kolumny jest przechowywany w: columnHeaderRange.

Następnie kod ten dodaje obramowanie i pogrubienie do zakresu nagłówka kolumny, tak jak w przypadku formatRowHeader(). Tutaj jest też używany Range.setFontStyle(fontStyle) do kursywania tekstu.

Dodanie hiperlinków do kolumny nagłówka jest bardziej złożone, więc formatColumnHeader() wywołuje polecenie hyperlinkColumnHeaders_(headerRange, numRows), aby zająć się tym zadaniem. Dzięki temu kod jest przejrzysty i czytelny.

hyperlinkColumnHeaders_(headerRange, numRows)

Ta funkcja pomocnicza najpierw identyfikuje indeksy kolumny (zakładamy, że ma on indeks 1) i kolumnę url. Wywołuje metodę columnIndexOf_('url'), by pobrać indeks kolumny URL. Jeśli nie odnaleziono kolumny url, metoda jest zamykana bez modyfikowania jakichkolwiek danych.

Funkcja uzyskuje nowy zakres (urlRange), który obejmuje adresy URL odpowiadające wierszom kolumn nagłówka. Służy do tego metoda Range.offset(rowOffset, columnOffset), która zapewnia, że 2 zakresy mają ten sam rozmiar. Zostaną pobrane wartości z kolumn headerColumn i url (headerValues i urlValues).

Następnie funkcja zapętla wartość każdej komórki z nagłówkiem kolumny i zastępuje ją formułą =HYPERLINK() Arkuszy stworzoną z nagłówka i url zawartości kolumny. Zmodyfikowane wartości nagłówka są następnie wstawiane do arkusza za pomocą Range.setValues(values).

Na koniec aby pomóc w utrzymaniu porządku w arkuszu i eliminowaniu zbędnych informacji, wywołujemy funkcję Sheet.deleteColumn(columnPosition) w celu usunięcia kolumny url.

columnIndexOf_(colName)

Ta funkcja pomocnicza jest po prostu prostą funkcją, wyszukującą w pierwszym wierszu arkusza określoną nazwę. Pierwsze trzy wiersze zawierają metody, które zostały już użyte do uzyskania listy nazw nagłówków kolumn z pierwszego wiersza arkusza kalkulacyjnego. Nazwy te są przechowywane w zmiennej nameNames.

Następnie funkcja sprawdza nazwy po kolei. Jeśli znajdzie wyrażenie zgodne z wyszukiwaną nazwą, zatrzymuje się i zwraca indeks tej kolumny. Jeśli znajdzie się na końcu listy nazw, ale nie znajdzie jej, zwróci -1, co oznacza, że nie udało się znaleźć nazwy.

Wyniki

Aby sprawdzić działanie funkcji formatowania, wykonaj te czynności:

  1. Zapisz projekt skryptu w edytorze Apps Script, jeśli jeszcze go nie masz.
  2. Kliknij pozycję menu Szybkie formaty > Formatuj nagłówek kolumny.

Powinny one wyglądać tak:

7497cf1b982aeff6.gif

Zautomatyzowano inne zadanie formatowania. Po sformatowaniu nagłówków kolumn i wierszy następnej sekcji dowiesz się, jak sformatować dane.

6. Formatowanie zbioru danych

Gdy masz już nagłówki, skorzystaj z funkcji, która formatuje pozostałe dane w arkuszu. Użyjemy tych opcji formatowania:

  • Zmienne kolory tła wiersza (nazywane pasami)
  • Zmienianie formatów daty
  • Stosowanie obramowania
  • Powiększanie wszystkich kolumn i wierszy

Teraz utworzysz funkcję formatDataset() i dodatkową metodę pomocną, która będzie stosować te formaty do danych w arkuszu.

Wdrażanie

Jak wcześniej dodaj funkcję, która automatyzuje formatowanie danych. Wykonaj te czynności:

  1. W edytorze Apps Script dodaj na końcu projektu skryptu następującą 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());
}
  1. Na końcu projektu skryptu dodaj następującą funkcję pomocniczą po funkcji formatDataset():
/** 
 * 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)");
}
  1. Zapisz projekt skryptu.

Weryfikacja kodu

Przeanalizujmy kod w każdej z tych dwóch funkcji oddzielnie:

formatDataset()

Ta funkcja działa podobnie do poprzednich funkcji formatu, które zostały już wdrożone. Najpierw pobiera zmienne do przechowywania odwołań do aktywnego arkusza (arkusza) i zakresu danych (fullDataRange).

Następnie używa metody Range.offset(rowOffset, columnOffset, numRows, numColumns) do utworzenia zakresu (noHeadersRange), który obejmuje wszystkie dane z arkusza oprócz nagłówków kolumn i wierszy. Następnie kod sprawdza, czy w tym zakresie występują nowe pasy (przy użyciu Range.getBandings()). Jest to konieczne, ponieważ Apps Script wysyła błąd, jeśli spróbujesz zastosować nowe pasy. Jeśli pasma nie istnieją, funkcja dodaje jasnoszare pasy za pomocą Range.applyRowBanding(bandingTheme, showHeader, showFooter). W przeciwnym razie funkcja będzie działać dalej.

Następny krok wywołuje funkcję pomocniczą formatDates_(colIndex) w celu sformatowania dat w kolumnie „release_date'” (opisanej poniżej). Ta kolumna jest określana za pomocą funkcji pomocniczej columnIndexOf_(colName) zaimplementowanej wcześniej.

Na koniec formatowanie jest dodawane jako kolejne obramowanie (jak dotychczas) i automatycznie zmienia rozmiar każdej kolumny i wiersza, aby dopasować je do zawartych w nich danych za pomocą metod Sheet.autoResizeColumns(columnPosition) i Sheet.autoResizeColumns(columnPosition).

formatDates_(colIndex)

Ta funkcja pomocnicza stosuje do kolumny konkretny format daty, używając podanego indeksu kolumn. Konkretnie dlatego, że format daty jest następujący: „Dzień miesiąca”, „Rok” (dzień tygodnia)”.

Najpierw funkcja sprawdza, czy indeks kolumny jest prawidłowy (wartość 0 lub większa). Jeśli nie, powróci bez wykonywania żadnych czynności. Takie sprawdzenie pozwala zapobiec błędom, które mogą wystąpić na przykład wtedy, gdy arkusz nie zawiera kolumny „release_date'”.

Po zweryfikowaniu indeksu kolumny funkcja uzyskuje zakres obejmujący tę kolumnę (nie licząc jej wiersza nagłówka) i używa formatowania, aby zastosować Range.setNumberFormat(numberFormat).

Wyniki

Aby sprawdzić działanie funkcji formatowania, wykonaj te czynności:

  1. Zapisz projekt skryptu w edytorze Apps Script, jeśli jeszcze go nie masz.
  2. Kliknij pozycję menu Szybkie formaty > Format zbioru danych.

Powinny one wyglądać tak:

3cfedd78b3e25f3a.gif

Zautomatyzowano jeszcze jedno zadanie formatowania. Skoro te polecenia formatowania są już dostępne, dodaj więcej danych, by je zastosować.

7. Pobieranie i formatowanie danych interfejsu API

Jak dotąd w ramach ćwiczeń z programowania omówiliśmy sposób korzystania z Apps Script jako alternatywnego sposobu formatowania arkusza kalkulacyjnego. Następnie napiszesz kod, który pobiera dane z publicznego interfejsu API, wstawia go do arkusza kalkulacyjnego i formatuje w sposób czytelny dla użytkownika.

Podczas ostatniego ćwiczenia z programowania dowiesz się, jak pobierać dane z interfejsu API. Te same techniki będziesz stosować tutaj. W tym ćwiczeniu do wypełnienia arkusza użyjemy publicznego interfejsu API Star Wars (SWAPI). W szczególności musisz użyć interfejsu API, aby uzyskać informacje o głównych postaciach występujących w pierwotnych 3 filmach o Gwiezdnych wojnach.

Twój kod wywoła interfejs API, by uzyskać dużą ilość danych JSON, przeanalizuj odpowiedź, umieść dane w nowym arkuszu i sformatuj arkusz.

Wdrażanie

W tej sekcji dodasz dodatkowe pozycje menu. Każdy element menu wywołuje skrypt kodu, który przekazuje zmienne związane z konkretnym elementem do funkcji głównej (createResourceSheet_()). Zaimplementujesz tę funkcję i trzy dodatkowe funkcje pomocnicze. Tak jak wcześniej, funkcje pomocnicze pomagają wyodrębniać logicznie selekcjonalne części zadań i zwiększać czytelność kodu.

Wykonaj te czynności:

  1. W edytorze Apps Script zaktualizuj funkcję onOpen() w projekcie skryptu, by odpowiadała tej wartości:
/**
 * 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();
}
  1. Zapisz projekt skryptu.
  2. W edytorze skryptów wybierz onOpen z listy funkcji i kliknij Uruchom. Uruchamia to polecenie onOpen(), aby utworzyć od nowa menu arkusza kalkulacyjnego z nowymi opcjami.
  3. Aby utworzyć plik Apps Script, obok opcji Pliki kliknij Dodaj plik dodaj plik > Skrypt.
  4. Nazwij nowy skrypt "API&quot i naciśnij Enter. Apps Script automatycznie dodaje rozszerzenie .gs do nazwy pliku skryptu.
  5. Zastąp kod w nowym pliku API.gs tym fragmentem:
/**
 * 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();

}
  1. 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);
}
  1. Zapisz projekt skryptu.

Weryfikacja kodu

Właśnie dodano dużą ilość kodu. Przyjrzyjmy się poszczególnym funkcjom, aby zrozumieć, jak działają:

onOpen()

Dodałeś kilka pozycji do menu Quick formats. Udało Ci się ustawić wiersz separatora, a następnie metodę Menu.addSubMenu(menu) utworzyć zagnieżdżoną strukturę menu z 3 nowymi elementami. Nowe elementy są dodawane przy użyciu metody Menu.addItem(caption, functionName).

Funkcje kodu

Wszystkie dodane pozycje menu mają podobny efekt: usiłują utworzyć arkusz z danymi pobranymi z SWAPI. Jedyna różnica polega na tym, że każdy skupia się na innym filmie.

Do utworzenia arkusza wygodniej jest utworzyć pojedynczą funkcję i zostać ona zaakceptowana przez parametr w celu określenia, którego filmu użyć. Metoda Menu.addItem(caption, functionName) nie pozwala jednak na przekazywanie do niej parametrów, gdy wywołuje je menu. Jak więc uniknąć trzykrotnego pisania tego samego kodu?

Odpowiedź to funkcje kodu. Są to proste funkcje, które mogą od razu wywoływać inną funkcję z określonymi parametrami.

Kod wykorzystuje 3 funkcje kodu: createPeopleSheetIV(), createPeopleSheetV() i createPeopleSheetVI(). Elementy menu są połączone z tymi funkcjami. Gdy użytkownik kliknie element menu, funkcja kodu wykonuje polecenie i automatycznie wywołuje funkcję kreatora głównego arkusza createResourceSheet_(resourceType, idNumber, episodeNumber), przekazując parametry odpowiednie dla elementu menu. W tym przypadku oznacza to, że musisz poprosić funkcję Kreatora arkuszy o utworzenie arkusza wypełnionego danymi głównych postaci z jednego z filmów o Gwiezdnych wojnach.

createResourceSheet_(resourceType, idNumber, episodeNumber)

To jest główna funkcja kreatora arkuszy dla tego ćwiczenia. Przy użyciu pewnych funkcji pomocnych pobiera dane interfejsu API, analizuje je, tworzy arkusz, zapisuje dane interfejsu API w arkuszu, a następnie formatuje arkusz przy użyciu funkcji utworzonych w poprzednich sekcjach. Sprawdźmy te szczegóły:

Najpierw funkcja wykorzystuje fetchApiResourceObject_(url) do wysłania żądania do interfejsu API w celu pobrania podstawowych informacji o filmie. Odpowiedź interfejsu API zawiera zbiór adresów URL, dzięki którym kod może uzyskać dodatkowe informacje o konkretnych osobach (określane tutaj jako materiały) z filmów. Kod zbiera je w tablicy resourceUrls.

Następnie kod używa fetchApiResourceObject_(url) wielokrotnie, 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, który opisuje inny znak niż film.

Obiekty danych zasobów mają kilka typowych kluczy, które są mapowane na informacje o tym znaku. Na przykład klucz „name'” odpowiada nazwie postaci z filmu. Zakładamy, że klucze każdego obiektu danych zasobu są identyczne, ponieważ mają korzystać z wspólnych struktur obiektów. Lista kluczy jest potrzebna później, więc kod przechowuje listę kluczy w metodzie resourceObjectKeys za pomocą metody JavaScript Object.keys().

Następnie funkcja kreatora wywołuje funkcję pomocniczą createNewSheet_(name), by utworzyć arkusz w miejscu, 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), aby dodać do arkusza wszystkie dane interfejsu API.

Na koniec wszystkie funkcje formatowania utworzone wcześniej są wywoływane do tych samych reguł formatowania dla nowych danych. Ponieważ nowy arkusz jest aktywny, kod może ponownie używać tych funkcji bez modyfikacji.

fetchApiResourceObject_(url)

Ta funkcja pomocnicza jest podobna do funkcji pomocniczej fetchBookData_(ISBN) dostępnej w poprzednim ćwiczeniu z wykorzystaniem danych (ćwiczenia z programowania). Odbiera podany adres URL i wybiera odpowiedź za pomocą metody UrlFetchApp.fetch(url, params). Następnie odpowiedź jest analizowana w obiekcie JSON za pomocą metod HTTPResponse.getContextText() i JavaScript JSON.parse(json). Powstały w ten sposób obiekt JSON.

createNewSheet_(name)

Ta funkcja pomocnicza jest dość prosta. Najpierw sprawdza, czy w arkuszu kalkulacyjnym znajduje się arkusz o podanej nazwie. Jeśli tak, zostanie aktywowany arkusz i zwrócony.

Jeśli arkusz nie istnieje, funkcja utworzy go za pomocą Spreadsheet.insertSheet(sheetName), aktywuje i zwróci nowy arkusz.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

Ta funkcja pomocnicza jest odpowiedzialna za wypełnienie nowego arkusza danymi interfejsu API. Jako parametry przyjmuje 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 reprezentuje wiersz.

Najpierw funkcja oblicza liczbę wierszy i kolumn potrzebnych do pokazania nowych danych interfejsu API. Jest to odpowiednio rozmiar listy zasobów i kluczy. Następnie określa zakres danych wyjściowych (resourceRange), w którym zostaną umieszczone dane, oraz dodatkowy wiersz z nagłówkami kolumn. Zmienna resourceValues zawiera tablicę wartości 2D wyodrębnioną z resourceRange.

Następnie funkcja zapętla każdy klucz obiektu z listy objectKeys. Klucz jest ustawiany jako nagłówek kolumny, a druga pętla przechodzi przez każdy obiekt zasobu. Dla każdej pary (wierszy, kolumny) odpowiadające jej dane interfejsu API są kopiowane do elementu resourceValues[row][column].

Po wypełnieniu arkusza resourceValues czyszczony jest arkusz docelowy z wykorzystaniem Sheet.clear() na wypadek, gdyby zawierał on dane z wcześniejszych kliknięć elementów menu. Nowe wartości są zapisywane w arkuszu.

Wyniki

Możesz zobaczyć wyniki swojej pracy, wykonując te czynności:

  1. Zapisz projekt skryptu w edytorze Apps Script, jeśli jeszcze go nie masz.
  2. Kliknij pozycję menu Szybkie formaty > Utwórz arkusz znaków > Odcinek IV.

Powinny one wyglądać tak:

d9c472ab518d8cef.gif

Masz teraz napisany kod do importowania danych do Arkuszy i automatycznego formatowania.

8. Podsumowanie

Gratulujemy ukończenia tego ćwiczenia z programowania. Znasz już kilka opcji formatowania Arkuszy, które możesz uwzględnić w swoich projektach Apps Script. Możesz też stworzyć imponującą aplikację, która importuje i formatuje duży zbiór danych interfejsu API.

Czy te ćwiczenia są przydatne?

Tak Nie

Czego się nauczysz

  • Stosowanie różnych operacji formatowania w Arkuszach za pomocą Apps Script.
  • Jak utworzyć menu podrzędne za pomocą funkcji onOpen().
  • Jak sformatować pobraną listę obiektów JSON w nowym arkuszu danych obejmującym Apps Script.

Co dalej

Z następnego ćwiczenia z tej playlisty dowiesz się, jak używać Apps Script do wizualizacji danych na wykresie i eksportowania wykresów do prezentacji w Prezentacjach Google.

Kolejne ćwiczenia z programowania znajdziesz w artykule Tworzenie wykresów i prezentowanie danych w Prezentacjach.