Podstawy Apps Script w Arkuszach Google nr 3: praca z danymi

1. Wstęp

Witamy w trzecim miejscu na playliście z podstawowymi informacjami o skrypcie aplikacji za pomocą Arkuszy Google.

Wykonując te ćwiczenia z programowania, dowiesz się, jak korzystać z operacji na danych, niestandardowych menu i publicznego pobierania danych przez interfejs API w Apps Script, aby zwiększyć wygodę korzystania z Arkuszy. Nadal będziesz pracować z zajęciami SpreadsheetApp, Spreadsheet, Sheet i Range, które zostały omówione we wspomnianych wcześniej ćwiczeniach z tej playlisty.

Czego się nauczysz

  • Importowanie danych z osobistego lub udostępnionego arkusza kalkulacyjnego na Dysku.
  • Jak utworzyć menu niestandardowe za pomocą funkcji onOpen().
  • Jak analizować wartości danych ciągu w Arkuszach Google i wprowadzać w nich zmiany.
  • Pobieranie i modyfikowanie danych obiektu JSON ze źródła publicznego interfejsu API.

Zanim zaczniesz

To trzecie z ćwiczeń z programowania, które obejmuje playlista Podstawy aplikacji i Arkuszy Google. Zanim rozpoczniesz te ćwiczenia z programowania, wykonaj te czynności:

  1. Makra i funkcje niestandardowe
  2. Arkusze kalkulacyjne, arkusze kalkulacyjne i zakresy

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

Ćwiczenia w tym ćwiczeniu z programowania wymagają arkusza kalkulacyjnego. Aby utworzyć arkusz kalkulacyjny do wykorzystania w następujących ćwiczeniach, wykonaj następujące czynności:

  1. Utwórz arkusz kalkulacyjny na Dysku Google. Możesz to zrobić w interfejsie Dysku, wybierając Nowy > Arkusze Google. Spowoduje to utworzenie i otwarcie nowego arkusza kalkulacyjnego. Plik zostanie zapisany w folderze Dysku.
  2. Kliknij tytuł arkusza kalkulacyjnego i zmień go z „"Arkusz kalkulacyjny bez tytułu” na „Manipulowanie danymi i menu niestandardowe”. Twój arkusz powinien wyglądać tak:

545c02912de7d112.png

  1. Aby otworzyć edytor skryptów, kliknij Rozszerzenia & Apps Script.
  2. Kliknij tytuł projektu Apps Script i zmień go z „"Projekt bez tytułu” na „Miksery danych i menu niestandardowe”. Kliknij Zmień nazwę, by zapisać zmianę nazwy.

Masz pusty arkusz kalkulacyjny i projekt. Teraz możesz rozpocząć moduł. Przejdź do następnej sekcji, aby dowiedzieć się więcej o menu niestandardowych.

3. Omówienie: importowanie danych za pomocą niestandardowej pozycji w menu

Apps Script umożliwia definiowanie niestandardowych menu, które mogą być wyświetlane w Arkuszach Google. Możesz też skorzystać z niestandardowych menu w Dokumentach, Prezentacjach i Formularzach Google. Po zdefiniowaniu niestandardowego elementu menu utwórz etykietę tekstową i połączysz ją z funkcją Apps Script w projekcie skryptu. Następnie możesz dodać menu do interfejsu, aby wyświetlić je w Arkuszach Google:

d6b694da6b8c6783.png

Gdy użytkownik kliknie element menu niestandardowego, powiązana z nim funkcja Apps Script wykonuje polecenie. Jest to szybki sposób na uruchamianie funkcji Apps Script bez konieczności otwierania edytora skryptu. Dzięki temu inni użytkownicy arkusza kalkulacyjnego mogą również uruchamiać kod bez konieczności wieszania, jak on działa i jak działa Apps Script. Dla nich to po prostu kolejny element menu.

Niestandardowe pozycje menu są zdefiniowane w funkcji onOpen() prostej reguły, o których dowiesz się w następnej sekcji.

4. Funkcja onOpen()

Proste wyzwalacze w Apps Script umożliwiają uruchamianie określonego kodu Apps Script w odpowiedzi na określone warunki lub zdarzenia. Tworząc regułę, określasz, jakie zdarzenie ma ją uruchamiać, i udostępniasz uruchomioną dla niego funkcję Apps Script.

Przykładem prostego wyzwalacza jest onOpen(). Są one łatwe do skonfigurowania – wystarczy dodać funkcję Apps Script o nazwie onOpen() i uruchamiać ją za każdym razem, gdy powiązany arkusz kalkulacyjny jest otwarty lub wczytany ponownie:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
 /* ... */ 
}

Wdrażanie

Utwórzmy niestandardowe menu.

  1. Zastąp ten kod w projekcie skryptu:
/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addToUi();
}
  1. Zapisz projekt skryptu.

Weryfikacja kodu

Sprawdźmy, jak działa ten kod. W onOpen() pierwszy wiersz korzysta z metody getUi() do pozyskania obiektu Ui reprezentującego interfejs aktywnego arkusza kalkulacyjnego, z którym jest powiązany ten skrypt.

W kolejnych 3 wierszach tworzysz menu (Book-list), dodajesz do niego pozycję menu (Load Book-list), a potem 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 działa po wybraniu elementu menu. W tym przypadku wybranie pozycji menu Load Book-list powoduje, że Arkusze próbują uruchomić funkcję loadBookList() (która jeszcze nie istnieje).

Wyniki

Uruchom tę funkcję teraz, aby sprawdzić, czy działa:

  1. W Arkuszach Google ponownie załaduj arkusz kalkulacyjny. Uwaga: zazwyczaj powoduje to zamknięcie karty w edytorze skryptów.
  2. Otwórz ponownie edytor skryptów, wybierając Narzędzia > Edytor skryptów.

Po ponownym wczytaniu arkusza kalkulacyjnego na pasku menu powinno pojawić się nowe menu Book-list:

687dfb214f2930ba.png

Gdy klikniesz Lista książek, zobaczysz menu, które się pojawi:

8a4a391fbabcb16a.png

W następnej sekcji należy utworzyć kod funkcji loadBookList() oraz podać jeden sposób interakcji z danymi w Apps Script: odczytywanie innych arkuszy kalkulacyjnych.

5. Importowanie danych z arkuszy kalkulacyjnych

Teraz, gdy masz już menu niestandardowe, możesz tworzyć funkcje, które można uruchomić, klikając daną pozycję menu.

Obecnie menu niestandardowe Book-list zawiera 1 pozycję menu: Load Book-list. ta funkcja jest wywoływana, gdy wybierzesz pozycję menu Load Book-list. loadBookList(), nie istnieje w Twoim skrypcie, więc wybranie opcji Lista książek > Załaduj listę książek powoduje błąd:

b94dcef066e7041d.gif

Możesz naprawić ten błąd, dodając funkcję loadBookList().

Wdrażanie

Nowy element menu ma wypełnić arkusz kalkulacyjny danymi, więc musisz zaimplementować tag loadBookList(), aby odczytać dane książek z innego arkusza kalkulacyjnego i skopiować je do tego arkusza:

  1. Dodaj do skryptu ten kod na stronie onOpen():
/** 
 * Creates a template book list based on the
 * provided 'codelab-book-list' sheet.
 */
function loadBookList(){
  // Gets the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Gets a different spreadsheet from Drive using
  // the spreadsheet's ID. 
  var bookSS = SpreadsheetApp.openById(
    "1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo" 
  );

  // Gets the sheet, data range, and values of the
  // spreadsheet stored in bookSS.
  var bookSheet = bookSS.getSheetByName("codelab-book-list");
  var bookRange = bookSheet.getDataRange();
  var bookListValues = bookRange.getValues();

  // Add those values to the active sheet in the current
  // spreadsheet. This overwrites any values already there.
  sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth()) 
    .setValues(bookListValues);
  
  // Rename the destination sheet and resize the data
  // columns for easier reading.
  sheet.setName("Book-list");
  sheet.autoResizeColumns(1, 3);
}
  1. Zapisz projekt skryptu.

Weryfikacja kodu

Jak więc działa ta funkcja? Funkcja loadBookList() używa głównie metod z klas Spreadsheet, Sheet i Range wprowadzonych w poprzednich ćwiczeniach z programowania. Biorąc to pod uwagę, możesz podzielić kod loadBookList() na te cztery sekcje:

1. Ustalanie arkusza docelowego

W pierwszym wierszu znajduje się odwołanie do SpreadsheetApp.getActiveSheet() bieżącego arkusza i zapisywanie go w zmiennej sheet. To jest arkusz, do którego zostaną skopiowane dane.

2. Rozpoznawanie danych źródłowych

Kilka następnych wierszy zawiera 4 zmienne odwołujące się do danych źródłowych, które pobierasz:

  • bookSS przechowuje odwołanie do arkusza kalkulacyjnego, z którego kod odczytuje dane. Kod znajduje arkusz na podstawie identyfikatora arkusza kalkulacyjnego. W tym przykładzie podaliśmy identyfikator źródłowego arkusza kalkulacyjnego do odczytania i otwórz go za pomocą metody SpreadsheetApp.openById(id).
  • bookSheet przechowuje odwołanie do arkusza w obrębie znaczników bookSS, który zawiera potrzebne dane. Kod identyfikuje arkusz, który ma zostać odczytany, korzystając z jego nazwy: codelab-book-list.
  • bookRange przechowuje odniesienie do zakresu danych w bookSheet. Metoda Sheet.getDataRange() zwraca zakres zawierający wszystkie niepuste komórki w arkuszu. To prosty sposób na zapewnienie zakresu obejmującego wszystkie dane w arkuszu bez uwzględniania pustych wierszy i kolumn.
  • bookListValues to tablica 2D zawierająca wszystkie wartości z komórek w komórce bookRange. Metoda Range.getValues() generuje tę tablicę, odczytując dane z arkusza źródłowego.

3. Kopiowanie danych ze źródła do miejsca docelowego

W następnej sekcji kodu dane bookListValues są kopiowane do sheet, a potem następuje zmiana nazwy arkusza:

4. Formatowanie arkusza docelowego

Sheet.setName(name) służy do zmiany nazwy arkusza docelowego na Book-list. W ostatnim wierszu funkcji Sheet.autoResizeColumns(startColumn, numColumns) zmienia rozmiar pierwszych trzech kolumn w arkuszu docelowym, co ułatwia odczytywanie nowych danych.

Wyniki

Możesz zobaczyć, jak działa ta funkcja. W Arkuszach Google wybierz Lista książek > Załaduj listę książek, aby uruchomić funkcję wypełniającą arkusz kalkulacyjny:

3C797e1e2b9fe641.gif

Arkusz zawiera listę tytułów książek, autorów i 13-cyfrowe numery ISBN. Z następnej sekcji dowiesz się, jak zmieniać i aktualizować dane na tej liście książek za pomocą manipulowania ciągami znaków i niestandardowych menu.

6. Omówienie: czyszczenie danych arkusza kalkulacyjnego

W arkuszu masz teraz dane książki. Każdy wiersz odnosi się do konkretnej książki, podając jej tytuł, autora i numer ISBN w oddzielnych kolumnach. Możesz jednak zauważyć pewne problemy z tymi nieprzetworzonymi danymi:

  1. W niektórych wierszach tytuł i autor są umieszczane w kolumnie tytułu połączone za pomocą przecinka lub ciągu "
  2. W niektórych wierszach brakuje tytułu lub autora książki.

W następnych sekcjach rozwiążesz te problemy, czyszcząc dane. W przypadku pierwszego problemu utworzysz funkcje, które odczytują kolumnę tytułową i rozdzielą tekst za każdym razem, gdy znajdzie się przecinek lub znak separatora, i umieszczenie odpowiednich podłańcucha autora i tytułu w odpowiednich kolumnach. W drugim przypadku możesz napisać kod, który automatycznie wyszukuje brakujące informacje o książce przy użyciu zewnętrznego interfejsu API, i dodaje je do Twojego arkusza.

7. Dodaj pozycje menu

Chcesz utworzyć 3 pozycje w menu, które będą kontrolować wykonywane przez Ciebie operacje czyszczenia danych.

Wdrażanie

Zaktualizujmy teraz onOpen(), by były potrzebne dodatkowe pozycje menu. Wykonaj następujące czynności:

  1. W projekcie skryptu zaktualizuj swój kod onOpen() tak, by pasował do tych:
/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addSeparator()
    .addItem(
      'Separate title/author at first comma', 'splitAtFirstComma')
    .addItem(
      'Separate title/author at last "by"', 'splitAtLastBy')
    .addSeparator()
    .addItem(
      'Fill in blank titles and author cells', 'fillInTheBlanks')
    .addToUi();
}
  1. Zapisz projekt skryptu.
  2. W edytorze skryptów wybierz onOpen z listy funkcji i kliknij Uruchom. Spowoduje to uruchomienie opcji onOpen() tak, aby przebudować menu arkusza kalkulacyjnego i nie trzeba było go ponownie wczytywać.

W nowym kodzie metoda Menu.addSeparator() tworzy w menu poziomy separator, w którym można uporządkować poszczególne elementy menu. Nowe pozycje w menu są dodawane pod nimi z etykietami Separate title/author at first comma, Separate title/author at last "by" i Fill in blank titles and author cells.

Wyniki

W arkuszu kalkulacyjnym kliknij menu Book-list, aby wyświetlić nowe pozycje menu:

580c806ce8fd4872.png

Kliknięcie tych nowych elementów powoduje wyświetlenie błędu, ponieważ nie zostały zaimplementowane odpowiadające im funkcje, więc przejdźmy dalej.

8. Podziel tekst na separatory

Zbiór danych zaimportowany do arkusza kalkulacyjnego zawiera kilka komórek, w których autor i tytuł są nieprawidłowo połączone w jednej komórce za pomocą przecinka:

ca91c43c4e51d6b5.png

Dzielenie ciągów tekstowych na osobne kolumny jest typowym zadaniem arkusza kalkulacyjnego. W Arkuszach Google jest dostępna funkcja SPLIT(), która dzieli ciągi znaków na kolumny. W zbiorze danych często występują jednak problemy, których nie można łatwo rozwiązać, korzystając z wbudowanych funkcji Arkuszy. W takich przypadkach możesz napisać kod Apps Script i wykonać złożone operacje potrzebne do wyczyszczenia i uporządkowania danych.

Zacznij wyczyścić dane, implementując funkcję o nazwie splitAtFirstComma(), która w przypadku znalezionych przecinków dzieli autora i tytuł na odpowiednie komórki.

Funkcja splitAtFirstComma() powinna wykonać następujące kroki:

  1. Pobierz zakres reprezentujący obecnie zaznaczone komórki.
  2. Sprawdź, czy komórki w zakresie mają przecinek.
  3. W przypadku znalezienia przecinków podziel go na dwa (i tylko dwa) podłańcuchy w miejscu pierwszego przecinka. Dla ułatwienia możesz założyć, że każdy przecinek wskazuje wzorzec &"autorzy], [tytuł]" Możesz też przyjąć, że jeśli w komórce wyświetla się wiele przecinków, rozdzielenie pierwszego przecinka w ciągu jest właściwe.
  4. Ustaw podłańcuchy jako nową treść odpowiednich tytułów i autorów.

Wdrażanie

Aby wykonać te czynności, użyj tych samych metod arkusza kalkulacyjnego, których używasz dotychczas, ale do manipulowania danymi z ciągu znaków musisz użyć JavaScriptu. Wykonaj te czynności:

  1. W edytorze Apps Script dodaj na końcu projektu skryptu następującą funkcję:
/**
 * Reformats title and author columns by splitting the title column
 * at the first comma, if present.
 */
function splitAtFirstComma(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where commas are found. Assumes the presence
  // of a comma indicates an "authors, title" pattern.
  for (var row = 0; row < titleAuthorValues.length; row++){
    var indexOfFirstComma =
        titleAuthorValues[row][0].indexOf(", ");

    if(indexOfFirstComma >= 0){
      // Found a comma, so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];

      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(indexOfFirstComma + 2);

      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(0, indexOfFirstComma);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}
  1. Zapisz projekt skryptu.

Weryfikacja kodu

Przyjrzyjmy się nowemu kodowi składającemu się z 3 głównych sekcji:

1. Pobierz wartości zaznaczonego tytułu

Pierwsze trzy wiersze zawierają 3 zmienne, które odnoszą się do bieżących danych w arkuszu:

  • activeRange odpowiada zakresowi aktualnie podświetlonemu przez użytkownika podczas wywoływania funkcji splitAtFirstComma(). Aby to zadanie było proste, można założyć, że użytkownik robi to wyłącznie podczas wyróżniania komórek w kolumnie A.
  • titleAuthorRange odpowiada nowemu zakresowi obejmującemu te same komórki, co activeRange, ale zawiera też jeszcze jedną kolumnę po prawej stronie. Do utworzenia pliku titleAuthorRange użyto metody Range.offset(rowOffset, columnOffset, numRows, numColumns). Kod potrzebuje tego rozszerzonego zakresu, ponieważ musi zawierać miejsce, w którym znajdzie się wszystkich autorów znalezionych w kolumnie „Tytuł”.
  • titleAuthorValues to tablica 2D danych wyodrębnionych z titleAuthorRange przy użyciu Range.getValues().

2. Sprawdź każdy tytuł i podziel go na pierwszy separator

W następnej sekcji zbadasz wartości w polu titleAuthorValues, aby znaleźć przecinki. JavaScript dla pętli służy do sprawdzania wszystkich wartości w pierwszej kolumnie tabeli titleAuthorValues. W przypadku znalezienia podłańcucha przecinków (", ") za pomocą metody JavaScript String indexOf() kod wykonuje te działania:

  1. Wartość ciągu tekstowego komórki zostanie skopiowana do zmiennej titlesAndAuthors.
  2. Lokalizację przecinka określa się za pomocą metody JavaScript String indexOf().
  3. Metoda JavaScript slice() jest wywoływana dwukrotnie, aby uzyskać podłańcuch przed separatorem i podłańcuch po separatorze.
  4. Podłańcuchy są skopiowane z powrotem do tablicy 2D titleAuthorValues, zastępując istniejące wartości w tej pozycji. Załóżmy, że wzorzec &[quot;[author]], [title]" powoduje odwrócenie kolejności dwóch podłańcuchów, aby tytuł znajdował się w pierwszej kolumnie, a autorzy – w drugiej.

Uwaga: jeśli kod nie znajdzie przecinka, dane w wierszu pozostaną bez zmian.

3. Kopiowanie nowych wartości do arkusza

Po sprawdzeniu wszystkich wartości komórek z tytułami zaktualizowana tablica 2D titleAuthorValues zostanie skopiowana do arkusza kalkulacyjnego za pomocą metody Range.setValues(values).

Wyniki

Teraz możesz zobaczyć, jak działa funkcja splitAtFirstComma(). Spróbuj uruchomić ją, wybierając pozycję menu Oddzielny tytuł/autor na pierwszym przecinku po wybraniu...

...jedna komórka:

a24763b60b305376.gif

...lub kilka komórek:

89c5c89b357d3713.gif

Udało Ci się utworzyć funkcję Apps Script do przetwarzania danych Arkuszy. Następnie zaimplementujesz drugą funkcję podziału.

9. Podziel tekst na separatorach &"

W pierwotnych danych może być widoczny inny problem. Tak jak niektóre formaty danych mają tytuły i autorów w jednej komórce jako "[autorzy], [tytuł]", inne komórki formatują autora i tytuły jako "[tytuł] autora [autorzy]":

41f0dd5ac63b62f4.png

Wdrażanie

Możesz rozwiązać ten problem, korzystając z tej samej metody jak w ostatniej sekcji, tworząc funkcję splitAtLastBy(). Ta funkcja jest podobna do funkcji splitAtFirstComma(). Jedyną rzeczywistą różnicą jest to, że szuka ona nieco innego wzorca tekstu. Zaimplementuj tę funkcję, wykonując następujące czynności:

  1. W edytorze Apps Script dodaj na końcu projektu skryptu następującą funkcję:
/** 
 * Reformats title and author columns by splitting the title column
 * at the last instance of the string " by ", if present.
 */
function splitAtLastBy(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where " by " substrings are found. Assumes
  // the presence of a " by " indicates a "title by authors"
  // pattern.
  for(var row = 0; row < titleAuthorValues.length; row++){
    var indexOfLastBy =
        titleAuthorValues[row][0].lastIndexOf(" by ");
    
    if(indexOfLastBy >= 0){
      // Found a " by ", so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];
      
      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);
      
      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 4);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}
  1. Zapisz projekt skryptu.

Weryfikacja kodu

Występuje kilka istotnych różnic między tym kodem a elementem splitAtFirstComma():

  1. Podłańcuch " by " jest używany jako separator ciągu, a nie ", ".
  2. Tutaj używana jest metoda JavaScript String.lastIndexOf(substring) zamiast String.indexOf(substring). Oznacza to, że jeśli w pierwszym ciągu znajduje się wiele podłańcuchów &by, każdy z nich musi się składać z ostatniego ciągu znaków & "by".
  3. Po podzieleniu ciągu pierwszy podłańcuch jest ustawiany jako tytuł, a drugi jako autor (jest to odwrotna kolejność w przypadku splitAtFirstComma()).

Wyniki

Teraz możesz zobaczyć, jak działa funkcja splitAtLastBy(). Spróbuj uruchomić je, wybierając na końcu pozycję Oddzielny tytuł/autor po wybraniu...

...jedna komórka:

4e6679e134145975.gif

...lub kilka komórek:

3C879c572c61e62f.gif

Ta sekcja ćwiczeń z programowania została ukończona. Teraz możesz używać Apps Script do odczytywania i modyfikowania danych ciągu w arkuszu, a także do wykonywania różnych poleceń Apps Script za pomocą niestandardowych menu.

Z następnej sekcji dowiesz się, jak ulepszyć ten zbiór danych, wypełniając puste komórki danymi pobranymi z publicznego interfejsu API.

10. Omówienie: pobieranie danych z publicznych interfejsów API

Do tej pory udało Ci się ulepszyć zbiór danych i rozwiązać problemy z tytułem oraz informacjami o autorze, ale nadal brakuje w nim niektórych informacji. Są one wyróżnione w komórkach poniżej:

af0dba8cb09d1a49.png

Nie możesz uzyskać brakujących danych, używając operacji na ciągach danych, które masz obecnie. Zamiast tego musisz pobrać brakujące dane z innego źródła. W tym celu możesz wysyłać żądania do zewnętrznych interfejsów API, które mogą dostarczać dodatkowe dane.

Interfejsy API to interfejsy programowania aplikacji. Jest to termin ogólny, ale jest to usługa, którą programy i skrypty mogą wywoływać w celu uzyskania informacji lub podjęcia określonych działań. W tej sekcji wywołujesz publicznie dostępny interfejs API, aby zażądać informacji o książkach, które można wstawić do pustych komórek w arkuszu.

Z tej sekcji dowiesz się, jak:

  • Żądanie danych książek z zewnętrznego źródła interfejsu API.
  • Wyodrębnij tytuł i informacje o autorze z zwróconych danych i zapisz je w arkuszu kalkulacyjnym.

11. Pobieranie danych zewnętrznych za pomocą funkcji UrlFetch

Zanim zagłębimy się w kod, który działa bezpośrednio z arkuszem kalkulacyjnym, dowiedz się, jak pracować z zewnętrznymi interfejsami API w języku Apps Script. W tym celu utwórz funkcję pomocniczą specjalnie na potrzeby żądania informacji o książce z publicznego interfejsu API Open Library.

Funkcja pomocnicza fetchBookData_(ISBN) jako parametr przyjmuje 13-cyfrowy numer ISBN książki i zwraca dane o niej. Łączy się z interfejsem Open Library API i pobiera informacje, a następnie analizuje zwrócony obiekt JSON.

Wdrażanie

Zaimplementuj tę funkcję pomocniczą, wykonując te czynności:

  1. W edytorze Apps Script dodaj na końcu skryptu ten kod:
/**
 * Helper function to retrieve book data from the Open Library
 * public API.
 *
 * @param {number} ISBN - The ISBN number of the book to find.
 * @return {object} The book's data, in JSON format.
 */
function fetchBookData_(ISBN){
  // Connect to the public API.
  var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
      + ISBN + "&jscmd=details&format=json";
  var response = UrlFetchApp.fetch(
      url, {'muteHttpExceptions': true});
  
  // Make request to API and get response before this point.
  var json = response.getContentText();
  var bookData = JSON.parse(json); 
  
  // Return only the data we're interested in.
  return bookData['ISBN:' + ISBN];
}
  1. Zapisz projekt skryptu.

Weryfikacja kodu

Kod ten dzieli się na dwie główne sekcje:

1. Żądanie interfejsu API

W pierwszych 2 wierszach fetchBookData_(ISBN) łączy się z publicznym interfejsem API Open Library za pomocą punktu końcowego URL-a interfejsu API i usługi pobierania adresów URL.

Zmienna url to po prostu ciąg adresu URL, tak jak adres internetowy. Wskazuje lokalizację na serwerach Open Library. Zawiera też 3 parametry (bibkeys, jscmd i format), które informują serwery Open Library o tym, jakich informacji potrzebujesz i jaką jest struktura odpowiedzi. W takim przypadku trzeba podać numer ISBN książki i poprosić o przesłanie szczegółowych informacji w formacie JSON.

Po utworzeniu ciągu adresu URL kod wysyła żądanie do lokalizacji i otrzymuje odpowiedź. Służy do tego metoda UrlFetchApp.fetch(url, params). Wysyła żądanie informacji na podany przez Ciebie zewnętrzny adres URL i zapisuje wynikową zmienną w zmiennej response. Oprócz adresu URL kod ustawia też opcjonalny parametr muteHttpExceptions na true. To ustawienie oznacza, że kod nie zostanie zatrzymany, jeśli żądanie spowoduje błąd interfejsu API. Jest natomiast zwracana odpowiedź o błędzie.

Żądanie zwraca obiekt HTTPResponse, który jest przechowywany w zmiennej response. Odpowiedzi HTTP obejmują kod odpowiedzi, nagłówki HTTP oraz główną treść odpowiedzi. Interesujące nas są tu główne treści JSON, więc kod musi wyodrębnić ten kod, a następnie przeanalizować go, by znaleźć i zwrócić żądane informacje.

2. Przeanalizuj odpowiedź API i zwróć informacje, które Cię interesują

W ostatnich 3 wierszach kodu metoda HTTPResponse.getContentText() zwraca główną treść odpowiedzi w postaci ciągu. Ciąg jest w formacie JSON, ale interfejs Open Library API określa dokładną treść i format. Metoda JSON.parse(jsonString) konwertuje ciąg JSON na obiekt JavaScript, dzięki czemu można łatwo wyodrębnić różne części danych. Na koniec funkcja zwraca dane odpowiadające numerowi ISBN książki.

Wyniki

Po zaimplementowaniu funkcji fetchBookData_(ISBN) inne funkcje kodu mogą wyszukiwać informacje o dowolnych książkach, używając ich numerów ISBN. Ta funkcja służy do wypełniania komórek w arkuszu kalkulacyjnym.

12. Zapisywanie danych interfejsu API w arkuszu kalkulacyjnym

Możesz teraz zaimplementować funkcję fillInTheBlanks(), która umożliwia:

  1. Znajdź brakujące dane dotyczące tytułów i autorów w aktywnym zakresie danych.
  2. Aby pobrać brakujące dane konkretnej książki, wywołaj interfejs Open Library API za pomocą metody pomocniczej fetchBookData_(ISBN).
  3. Zaktualizuj brakujące wartości tytułu lub autora w odpowiednich komórkach.

Wdrażanie

Zaimplementuj nową funkcję, wykonując te czynności:

  1. W edytorze Apps Script dodaj na końcu projektu skryptu ten kod:
/**
 * Fills in missing title and author data using Open Library API
 * calls.
 */ 
function fillInTheBlanks(){
  // Constants that identify the index of the title, author,
  // and ISBN columns (in the 2D bookValues array below). 
  var TITLE_COLUMN = 0;
  var AUTHOR_COLUMN = 1;
  var ISBN_COLUMN = 2;

  // Get the existing book information in the active sheet. The data
  // is placed into a 2D array.
  var dataRange = SpreadsheetApp.getActiveSpreadsheet()
    .getDataRange();
  var bookValues = dataRange.getValues();

  // Examine each row of the data (excluding the header row).
  // If an ISBN is present, and a title or author is missing,
  // use the fetchBookData_(isbn) method to retrieve the
  // missing data from the Open Library API. Fill in the
  // missing titles or authors when they're found.
  for(var row = 1; row < bookValues.length; row++){   
    var isbn = bookValues[row][ISBN_COLUMN];
    var title = bookValues[row][TITLE_COLUMN];
    var author = bookValues[row][AUTHOR_COLUMN];
   
    if(isbn != "" && (title === "" || author === "") ){
      // Only call the API if you have an ISBN number and
      // either the title or author is missing.
      var bookData = fetchBookData_(isbn);

      // Sometimes the API doesn't return the information needed.
      // In those cases, don't attempt to update the row.
      if (!bookData || !bookData.details) {
        continue;
      }

      // The API might not return a title, so only fill it in
      // if the response has one and if the title is blank in
      // the sheet.
      if(title === "" && bookData.details.title){
        bookValues[row][TITLE_COLUMN] = bookData.details.title; 
      }

      // The API might not return an author name, so only fill it in
      // if the response has one and if the author is blank in
      // the sheet.
      if(author === "" && bookData.details.authors
          && bookData.details.authors[0].name){
        bookValues[row][AUTHOR_COLUMN] =
          bookData.details.authors[0].name; 
      }
    }
  }
  
  // Insert the updated book data values into the spreadsheet.
  dataRange.setValues(bookValues);
}
  1. Zapisz projekt skryptu.

Weryfikacja kodu

Ten kod składa się z trzech sekcji:

1. Odczytywanie istniejących informacji o książce

Pierwsze 3 wiersze funkcji definiują stałe, aby zwiększyć czytelność kodu. W 2 kolejnych wierszach zmienna bookValues jest używana do przechowywania lokalnej kopii informacji z arkusza. Kod odczytuje informacje z pola bookValues, używa interfejsu API do wypełniania brakujących informacji i zapisywania tych wartości z powrotem do arkusza kalkulacyjnego.

2. Pobieranie brakujących informacji za pomocą funkcji pomocniczej

Kod znajduje się w różnych wierszach wiersza bookValues i znajduje brakujące tytuły lub autorów. Aby zmniejszyć liczbę wywołań interfejsu API przy jednoczesnej poprawie wydajności, kod wywołuje interfejs API tylko wtedy, gdy są spełnione te warunki:

  1. Kolumna „ISBN” w wierszu ma wartość.
  2. Tytuł lub komórka autora w wierszu są puste.

Jeśli warunki są spełnione, kod wywołuje interfejs API za pomocą zaimplementowanej wcześniej funkcji pomocniczej fetchBookData_(isbn) i umieszcza wynik w zmiennej bookData. Powinien zawierać brakujące informacje, które chcesz wstawić do arkusza.

Jedyne zadanie to dodanie informacji bookData do arkusza kalkulacyjnego. Trzeba jednak pamiętać o pewnych zastrzeżeniach. Niestety, publiczne interfejsy API, takie jak Open Library Book API, czasami nie zawierają informacji, o które prosisz, lub czasami występują inne problemy, które uniemożliwiają ich dostarczenie. Jeśli zakładasz, że każde żądanie API zostanie zrealizowane, kod nie będzie wystarczająco zaawansowany do obsługi nieoczekiwanych błędów.

Aby kod mógł obsługiwać błędy interfejsu API, musi on najpierw sprawdzić, czy odpowiedź API jest prawidłowa. Gdy kod ma bookData, wykonuje prostą kontrolę, aby sprawdzić, czy bookData i bookData.details istnieją. Jeśli brakuje żadnego z nich, oznacza to, że interfejs API nie miał potrzebnych danych. W takiej sytuacji polecenie continue informuje kod o pominięciu tego wiersza – nie można wypełnić brakujących komórek, ale przynajmniej skrypt nie ulegnie awarii.

3. Zapisanie ponownie zaktualizowanych informacji w arkuszu

Ostatnia część kodu ma podobne kontrole, które weryfikują, czy interfejs API zwraca tytuł i informacje o autorze. Tablica bookValues aktualizuje tablicę tylko wtedy, gdy oryginalny tytuł lub komórka autora są puste, a interfejs API zwraca wartość, którą możesz w nim umieścić.

Zapętla się po sprawdzeniu wszystkich wierszy w arkuszu. Ostatnim krokiem jest zapisanie zaktualizowanej tablicy bookValues w arkuszu kalkulacyjnym za pomocą Range.setValues(values).

Wyniki

Teraz możesz zakończyć czyszczenie danych książki. Wykonaj następujące czynności:

  1. Jeśli zakres nie jest jeszcze zaznaczony, zaznacz zakres A2:A15 w arkuszu i kliknij Lista książek (na początku i nazwisku/pierwszym autorze), aby rozwiązać problemy.
  2. Jeśli zakres nie jest jeszcze zaznaczony, zaznacz w arkuszu zakres A2:A15 i wybierz Książka i oddzielny tytuł na końcu, aby wyczyścić problemy.
  3. Aby wypełnić pozostałe komórki, wybierz Lista książek > wypełnij puste tytuły i komórki autora:

826675a3437adbdb.gif

13. Podsumowanie

Gratulujemy ukończenia tego ćwiczenia z programowania. Wiesz już, jak tworzyć niestandardowe menu, które aktywują różne części kodu Apps Script. Wiesz też, jak importować dane do Arkuszy Google przy użyciu usług Apps Script i publicznych interfejsów API. Jest to typowa operacja w arkuszu kalkulacyjnym. Pozwala ona importować dane z wielu różnych źródeł. Wiesz już, jak używać usług Apps Script oraz języka JavaScript do odczytywania, przetwarzania i wstawiania danych z arkuszy kalkulacyjnych.

Czy te ćwiczenia są przydatne?

Tak Nie

Czego się nauczysz

  • Importowanie danych z arkusza kalkulacyjnego Google.
  • Jak utworzyć menu niestandardowe w funkcji onOpen().
  • Jak analizować wartości danych w postaci ciągu i dokonywać związanych z nimi czynności.
  • Jak wywoływać publiczne interfejsy API przy użyciu usługi pobierania adresów URL.
  • Jak analizować dane obiektów JSON pobrane z publicznego źródła API.

Co dalej

Następne szkolenie z tej playlisty szczegółowo omówi formatowanie danych w arkuszu kalkulacyjnym.

Kolejne ćwiczenia z programowania znajdziesz w artykule Formatowanie danych.