Podstawy Apps Script w Arkuszach Google – część 3. Praca z danymi

1. Wprowadzenie

Witamy w trzeciej części playlisty z ćwiczeniami dotyczącymi podstaw Apps Script w Arkuszach Google.

Po ukończeniu tego ćwiczenia dowiesz się, jak używać w Apps Script manipulacji danymi, niestandardowych menu i pobierania danych z publicznego interfejsu API, aby ułatwić sobie korzystanie z Arkuszy. Będziesz kontynuować pracę z klasami SpreadsheetApp, Spreadsheet, Sheet i Range, które zostały wprowadzone w poprzednich ćwiczeniach z tej playlisty.

Czego się nauczysz

  • Jak importować dane z osobistego lub udostępnionego arkusza kalkulacyjnego na Dysku.
  • Jak utworzyć menu niestandardowe za pomocą funkcji onOpen().
  • Jak analizować i modyfikować wartości danych tekstowych w komórkach Arkuszy Google.
  • Jak pobierać i przetwarzać dane obiektu JSON ze źródła publicznego interfejsu API.

Zanim zaczniesz

To trzecie szkolenie z serii Podstawy Apps Script w Arkuszach Google. Zanim zaczniesz to ćwiczenie, wykonaj poprzednie ćwiczenia:

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

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

Ćwiczenia w tym przewodniku wymagają arkusza kalkulacyjnego. Aby utworzyć arkusz kalkulacyjny, który będzie używany w tych ćwiczeniach, wykonaj te 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 na Dysku.
  2. Kliknij tytuł arkusza kalkulacyjnego i zmień go z „Arkusz kalkulacyjny bez tytułu” na „Manipulowanie danymi i menu niestandardowe”. 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 „Bez tytułu” na „Data Manipulation and Custom Menus” (Manipulowanie danymi i menu niestandardowe). Aby zapisać zmianę tytułu, kliknij Zmień nazwę.

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

3. Omówienie: importowanie danych za pomocą niestandardowego elementu menu

Apps Script umożliwia definiowanie niestandardowych menu, które mogą pojawiać się w Arkuszach Google. Możesz też używać niestandardowych menu w Dokumentach Google, Prezentacjach Google i Formularzach Google. Podczas definiowania niestandardowej pozycji menu tworzysz etykietę tekstową i łączysz ją z funkcją Apps Script w projekcie skryptu. Następnie możesz dodać menu do interfejsu, aby wyświetlało się w Arkuszach Google:

d6b694da6b8c6783.png

Gdy użytkownik kliknie element menu niestandardowego, uruchomi się powiązana z nim funkcja Apps Script. To szybki sposób na uruchamianie funkcji Apps Script bez konieczności otwierania edytora skryptów. Umożliwia też innym użytkownikom arkusza kalkulacyjnego wykonywanie kodu bez konieczności poznawania jego działania ani działania Apps Script. Dla nich to po prostu kolejna pozycja w menu.

Niestandardowe pozycje menu są zdefiniowane w funkcji onOpen() prostego aktywatora, o której dowiesz się więcej 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. Podczas tworzenia aktywatora określasz, jakie zdarzenie powoduje jego uruchomienie, i podajesz funkcję Apps Script, która ma być uruchamiana w przypadku tego zdarzenia.

onOpen() to przykład prostego wyzwalacza. Są łatwe w konfiguracji – wystarczy napisać funkcję Apps Script o nazwie onOpen(), a Apps Script będzie ją uruchamiać za każdym razem, gdy powiązany arkusz kalkulacyjny zostanie otwarty lub ponownie wczytany:

/**  * 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() {  /* ... */  }

Implementacja

Utwórzmy menu niestandardowe.

  1. Zastąp kod w projekcie skryptu tym kodem:
/**  * 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

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 3 wiersze tworzą menu (Book-list), dodają do niego pozycję (Load Book-list), 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. W tym przypadku wybranie pozycji menu Load Book-list spowoduje, że Arkusze spróbują uruchomić funkcję loadBookList() (która jeszcze nie istnieje).

Wyniki

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

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

Po ponownym załadowaniu arkusza kalkulacyjnego na pasku menu powinna pojawić się nowa ikona Book-list:

687dfb214f2930ba.png

Klikając Lista książek, możesz wyświetlić menu:

8a4a391fbabcb16a.png

W następnej sekcji utworzymy kod funkcji loadBookList() i przedstawimy jeden ze sposobów interakcji z danymi w Apps Script: odczytywanie innych arkuszy kalkulacyjnych.

5. Importowanie danych z arkuszy kalkulacyjnych

Po utworzeniu menu niestandardowego możesz utworzyć funkcje, które można uruchamiać, klikając element menu.

Obecnie menu niestandardowe Book-list zawiera 1 element: Load Book-list. funkcja wywoływana po wybraniu elementu menu Load Book-list, loadBookList(), nie istnieje w skrypcie, więc wybranie Lista książek > Wczytaj listę książek spowoduje błąd:

b94dcef066e7041d.gif

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

Implementacja

Chcesz, aby nowy element menu wypełniał arkusz kalkulacyjny danymi, z którymi możesz pracować, więc zaimplementujesz funkcję loadBookList(), która będzie odczytywać dane książek z innego arkusza kalkulacyjnego i kopiować je do tego:

  1. Dodaj ten kod do skryptu w sekcji 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 działa ta funkcja? Funkcja loadBookList() korzysta z metod głównie z klas Spreadsheet, Sheet i Range, które zostały przedstawione w poprzednich ćwiczeniach z kodem. Mając na uwadze te pojęcia, możesz podzielić kod loadBookList() na te 4 sekcje:

1. Określ arkusz docelowy

Pierwszy wiersz używa funkcji SpreadsheetApp.getActiveSheet(), aby uzyskać odwołanie do bieżącego obiektu arkusza i zapisać je w zmiennej sheet. To arkusz, do którego zostaną skopiowane dane.

2. Zidentyfikuj dane źródłowe

W kolejnych wierszach tworzone są 4 zmienne, które odwołują się do pobieranych danych źródłowych:

  • bookSS zawiera odwołanie do arkusza kalkulacyjnego, z którego kod odczytuje dane. Kod wyszukuje arkusz kalkulacyjny na podstawie jego identyfikatora. W tym przykładzie podaliśmy identyfikator arkusza źródłowego, z którego będziemy odczytywać dane, i otworzyliśmy arkusz za pomocą metody SpreadsheetApp.openById(id).
  • bookSheet przechowuje odwołanie do arkusza w bookSS, który zawiera potrzebne dane. Kod identyfikuje arkusz, z którego ma odczytywać dane, za pomocą jego nazwy – codelab-book-list.
  • bookRange przechowuje odwołanie do zakresu danych w bookSheet. Metoda Sheet.getDataRange() zwraca zakres zawierający wszystkie niepuste komórki w arkuszu. To prosty sposób na uzyskanie zakresu obejmującego wszystkie dane w arkuszu bez uwzględniania pustych wierszy i kolumn.
  • bookListValues to tablica dwuwymiarowa zawierająca wszystkie wartości pobrane z komórek w bookRange. Metoda Range.getValues() generuje tę tablicę, odczytując dane z arkusza źródłowego.

3. Skopiuj dane ze źródła do miejsca docelowego

W następnej sekcji kodu dane z bookListValues są kopiowane do sheet, a następnie arkusz jest zmieniany:

4. Sformatuj arkusz docelowy

Symbol Sheet.setName(name) służy do zmiany nazwy arkusza docelowego na Book-list. Ostatni wiersz funkcji używa Sheet.autoResizeColumns(startColumn, numColumns) do zmiany rozmiaru pierwszych 3 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 > Wczytaj listę książek, aby uruchomić funkcję wypełniania arkusza kalkulacyjnego:

3c797e1e2b9fe641.gif

Teraz masz arkusz z listą tytułów książek, autorów i 13-cyfrowych numerów ISBN. W następnej sekcji dowiesz się, jak modyfikować i aktualizować dane na tej liście książek za pomocą manipulacji ciągami znaków i menu niestandardowych.

6. Omówienie: oczyszczanie danych arkusza kalkulacyjnego

W arkuszu znajdują się teraz informacje o książce. Każdy wiersz odnosi się do konkretnej książki i zawiera jej tytuł, autora i numer ISBN w osobnych kolumnach. Możesz jednak zauważyć pewne problemy z tymi surowymi danymi:

  1. W przypadku niektórych wierszy tytuł i autor są umieszczone razem w kolumnie tytułu i połączone przecinkiem lub ciągiem znaków „by”.
  2. W niektórych wierszach brakuje tytułu lub autora książki.

W kolejnych sekcjach rozwiążesz te problemy, oczyszczając dane. W przypadku pierwszego problemu utworzysz funkcje, które odczytują kolumnę tytułu i dzielą tekst, gdy tylko znajdą przecinek lub separator „ by ”, umieszczając odpowiednie podciągi autora i tytułu w prawidłowych kolumnach. W przypadku drugiego problemu napiszesz kod, który automatycznie wyszukuje brakujące informacje o książce za pomocą zewnętrznego interfejsu API i dodaje je do arkusza.

7. Dodawanie pozycji menu

Utwórz 3 elementy menu, aby kontrolować operacje czyszczenia danych, które będziesz wdrażać.

Implementacja

Zaktualizujmy onOpen(), aby uwzględnić dodatkowe pozycje menu, których będziesz potrzebować. Wykonaj te czynności:

  1. W projekcie skryptu zaktualizuj kod onOpen(), aby był zgodny z tym kodem:
/**  * 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 usługi onOpen(), która ponownie utworzy menu arkusza kalkulacyjnego, dzięki czemu nie musisz go ponownie wczytywać.

W tym nowym kodzie metoda Menu.addSeparator() tworzy w menu poziomą linię podziału, która pomaga zachować wizualną organizację grup powiązanych elementów menu. Nowe elementy menu zostaną dodane poniżej 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 błąd, ponieważ nie zostały jeszcze zaimplementowane odpowiadające im funkcje. Zróbmy to teraz.

8. Dzielenie tekstu za pomocą przecinków

Zbiór danych zaimportowany do arkusza 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 to częste zadanie w arkuszach kalkulacyjnych. Arkusze Google udostępniają funkcję SPLIT(), która dzieli ciągi znaków na kolumny. Zbiory danych często zawierają jednak problemy, których nie można łatwo rozwiązać za pomocą wbudowanych funkcji Arkuszy. W takich przypadkach możesz napisać kod Apps Script, który wykona złożone operacje potrzebne do oczyszczenia i uporządkowania danych.

Zacznij od wyczyszczenia danych, wdrażając najpierw funkcję splitAtFirstComma(), która dzieli autora i tytuł na odpowiednie komórki, gdy znajdzie przecinki.

Funkcja splitAtFirstComma() powinna wykonać te czynności:

  1. Pobierz zakres reprezentujący obecnie zaznaczone komórki.
  2. Sprawdź, czy komórki w zakresie zawierają przecinek.
  3. Jeśli w ciągu znaków występują przecinki, podziel go na 2 (i tylko 2) podciągi w miejscu pierwszego przecinka. Aby uprościć sprawę, możesz założyć, że każdy przecinek wskazuje wzorzec ciągu „[autorzy], [tytuł]”. Możesz też założyć, że jeśli w komórce występuje kilka przecinków, należy podzielić ciąg znaków na pierwszy przecinek.
  4. Ustaw podciągi jako nową zawartość odpowiednich komórek tytułu i autora.

Implementacja

Aby wykonać te czynności, użyjesz tych samych metod usługi Arkusze kalkulacyjne, których używałeś(-aś) wcześniej, ale musisz też użyć JavaScriptu do manipulowania danymi tekstowymi. Wykonaj te czynności:

  1. W edytorze Apps Script dodaj tę funkcję na końcu projektu skryptu:
/**  * 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, który składa się z 3 głównych sekcji:

1. Pobierz wyróżnione wartości tytułu

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

  • activeRange reprezentuje zakres, który użytkownik ma obecnie zaznaczony, gdy wywołano funkcję splitAtFirstComma(). Aby uprościć to ćwiczenie, możemy założyć, że użytkownik wykonuje tę czynność tylko podczas wyróżniania komórek w kolumnie A.
  • titleAuthorRange reprezentuje nowy zakres, który obejmuje te same komórki co activeRange, ale zawiera też jeszcze jedną kolumnę po prawej stronie. titleAuthorRange jest tworzony za pomocą metody Range.offset(rowOffset, columnOffset, numRows, numColumns). Kod potrzebuje tego rozszerzonego zakresu, ponieważ musi mieć miejsce na umieszczenie autorów znalezionych w kolumnie tytułu.
  • titleAuthorValues to dwuwymiarowa tablica danych wyodrębnionych z titleAuthorRange za pomocą funkcji Range.getValues().

2. Sprawdź każdy tytuł i podziel go na podstawie pierwszego znalezionego przecinka

W następnej sekcji sprawdzimy, czy w wartościach w kolumnie titleAuthorValues występują przecinki. Pętla JavaScript For służy do sprawdzania wszystkich wartości w pierwszej kolumnie titleAuthorValues. Gdy za pomocą metody JavaScript String indexOf() zostanie znaleziony podciąg z przecinkiem (", "), kod wykona te czynności:

  1. Wartość ciągu znaków komórki jest kopiowana do zmiennej titlesAndAuthors.
  2. Położenie przecinka jest określane za pomocą metody JavaScript String indexOf().
  3. Metoda JavaScript String slice() jest wywoływana 2 razy, aby uzyskać podciąg przed separatorem w postaci przecinka i podciąg po nim.
  4. Podciągi są kopiowane z powrotem do dwuwymiarowej tablicy titleAuthorValues, co powoduje zastąpienie istniejących wartości w tej pozycji. Zakładamy wzorzec „[autorzy], [tytuł]”, więc kolejność 2 podciągów jest odwrócona, aby tytuł znalazł się w pierwszej kolumnie, a autorzy w drugiej.

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

3. Skopiuj nowe wartości z powrotem do arkusza

Po sprawdzeniu wszystkich wartości komórek tytułu zaktualizowana dwuwymiarowa tablica titleAuthorValues jest kopiowana z powrotem do arkusza kalkulacyjnego za pomocą metody Range.setValues(values).

Wyniki

Możesz teraz zobaczyć, jak działa funkcja splitAtFirstComma(). Spróbuj uruchomić go, wybierając pozycję menu Separate title/author at first comma (Oddziel tytuł i autora po pierwszym przecinku) po wybraniu...

…jedna komórka:

a24763b60b305376.gif

...lub wiele komórek:

89c5c89b357d3713.gif

Masz już funkcję Apps Script, która przetwarza dane z Arkuszy. Następnie zaimplementujesz drugą funkcję dzielenia.

9. Podzielenie tekstu za pomocą separatorów „by”

Przyjrzyj się oryginalnym danym, aby zobaczyć kolejny problem. Podobnie jak w przypadku niektórych formatów danych, tytuły i autorzy są umieszczani w jednej komórce w formacie „[autorzy], [tytuł]”, w innych komórkach autor i tytuł są formatowane jako „[tytuł] autorstwa [autorzy]”:

41f0dd5ac63b62f4.png

Implementacja

Ten problem możesz rozwiązać, stosując tę samą technikę co w ostatniej sekcji, czyli tworząc funkcję o nazwie splitAtLastBy(). Ta funkcja działa podobnie jak splitAtFirstComma() – jedyna różnica polega na tym, że wyszukuje nieco inny wzorzec tekstu. Aby zaimplementować tę funkcję, wykonaj te czynności:

  1. W edytorze Apps Script dodaj tę funkcję na końcu projektu skryptu:
/**   * 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

Istnieje kilka kluczowych różnic między tym kodem a splitAtFirstComma():

  1. Ciąg znaków „ by ” jest używany jako separator ciągów znaków zamiast „, ”.
  2. Zamiast metody String.indexOf(substring) używana jest tu metoda JavaScript String.lastIndexOf(substring). Oznacza to, że jeśli w ciągu początkowym występuje kilka podciągów „ by ”, wszystkie z nich z wyjątkiem ostatniego są uznawane za część tytułu.by
  3. Po podzieleniu ciągu znaków pierwszy podciąg jest ustawiany jako tytuł, a drugi jako autor (jest to odwrotna kolejność niż w przypadku splitAtFirstComma()).

Wyniki

Możesz teraz zobaczyć, jak działa funkcja splitAtLastBy(). Spróbuj uruchomić go, wybierając pozycję menu Separate title/author at last "by" (Oddziel tytuł i autora przy ostatnim słowie „by”) po wybraniu…

…jedna komórka:

4e6679e134145975.gif

...lub wiele komórek:

3c879c572c61e62f.gif

Ta część ćwiczenia została ukończona. Możesz teraz używać Apps Script do odczytywania i modyfikowania danych tekstowych w arkuszu oraz używać niestandardowych menu do wykonywania różnych poleceń Apps Script.

W następnej sekcji dowiesz się, jak jeszcze bardziej 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ę poprawić formatowanie tytułów i autorów, ale w zbiorze danych nadal brakuje niektórych informacji, które są wyróżnione w komórkach poniżej:

af0dba8cb09d1a49.png

Nie możesz uzyskać brakujących danych, wykonując operacje na ciągach znaków na podstawie danych, które masz obecnie. Zamiast tego musisz pobrać brakujące dane z innego źródła. Możesz to zrobić w Apps Script, wysyłając żądanie informacji do zewnętrznych interfejsów API, które mogą dostarczyć dodatkowe dane.

Interfejsy API to interfejsy programowania aplikacji. Jest to termin ogólny, ale w zasadzie chodzi o usługę, do której programy i skrypty mogą się odwoływać, aby poprosić o informacje lub podjąć określone działania. W tej sekcji wywołujesz publicznie dostępny interfejs API, aby poprosić o informacje o książkach, które możesz wstawić do pustych komórek w arkuszu.

W tej sekcji dowiesz się, jak:

  • Wysyłanie żądań danych o książkach do zewnętrznego źródła interfejsu API.
  • Wyodrębnij z zwróconych danych informacje o tytule i autorze i zapisz je w arkuszu kalkulacyjnym.

11. Pobieranie danych zewnętrznych za pomocą UrlFetch

Zanim zaczniesz pisać kod, który działa bezpośrednio z arkuszem kalkulacyjnym, możesz dowiedzieć się, jak korzystać z zewnętrznych interfejsów API w Apps Script, tworząc funkcję pomocniczą specjalnie do wysyłania zapytań o informacje o książkach z publicznego interfejsu Open Library API.

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

Implementacja

Aby zaimplementować tę funkcję pomocniczą, wykonaj te czynności:

  1. W edytorze Apps Script dodaj ten kod na końcu skryptu:
/**  * 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 jest podzielony na 2 główne sekcje:

1. Żądanie interfejsu API

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

Zmienna url to po prostu ciąg znaków URL, np. adres internetowy. Wskazuje lokalizację na serwerach Open Library. Zawiera też 3 parametry (bibkeys, jscmd i format), które informują serwery Open Library, o jakie informacje prosisz i jak ma wyglądać odpowiedź. W tym przypadku podajesz numer ISBN książki i prosisz o szczegółowe informacje 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 otrzymaną odpowiedź w zmiennej response. Oprócz adresu URL kod ustawia opcjonalny parametr muteHttpExceptions na wartość true. To ustawienie oznacza, że kod nie zostanie zatrzymany, jeśli żądanie spowoduje błąd interfejsu API. Zamiast tego zwracany jest komunikat o błędzie.

Żądanie zwraca obiekt HTTPResponse, który jest przechowywany w zmiennej response. Odpowiedzi HTTP zawierają kod odpowiedzi, nagłówki HTTP i główną treść odpowiedzi. Interesujące nas informacje to główna treść JSON, więc kod musi ją wyodrębnić, a następnie przeanalizować JSON, aby znaleźć i zwrócić żądane informacje.

2. Przeanalizuj odpowiedź interfejsu API i zwróć interesujące Cię informacje

W 3 ostatnich wierszach kodu metoda HTTPResponse.getContentText() zwraca główną treść odpowiedzi w postaci ciągu znaków. Ten ciąg znaków jest w formacie JSON, ale dokładną treść i format określa interfejs Open Library API. Metoda JSON.parse(jsonString) przekształca ciąg znaków JSON w 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 w Twoim kodzie mogą wyszukiwać informacje o dowolnej książce za pomocą jej numeru ISBN. Ta funkcja pomoże Ci wypełnić komórki w arkuszu kalkulacyjnym.

12. Zapisywanie danych z interfejsu API w arkuszu kalkulacyjnym

Możesz teraz wdrożyć funkcję fillInTheBlanks(), która wykonuje te czynności:

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

Implementacja

Aby wdrożyć tę nową funkcję, wykonaj te czynności:

  1. W edytorze Apps Script dodaj ten kod na końcu projektu skryptu:
/**  * 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

Kod jest podzielony na 3 sekcje:

1. Przeczytaj informacje o książce

Pierwsze 3 wiersze funkcji definiują stałe, które ułatwiają czytanie kodu. W kolejnych 2 wierszach zmienna bookValues służy do przechowywania lokalnej kopii informacji o arkuszu. Kod odczyta informacje z bookValues, użyje interfejsu API do uzupełnienia brakujących informacji i zapisze te wartości z powrotem w arkuszu kalkulacyjnym.

2. Pobierz brakujące informacje za pomocą funkcji pomocniczej

Kod przechodzi przez każdy wiersz w bookValues, aby znaleźć brakujące tytuły lub autorów. Aby zmniejszyć liczbę wywołań interfejsu API przy jednoczesnym zwiększeniu wydajności, kod wywołuje interfejs API tylko wtedy, gdy spełnione są te warunki:

  1. Kolumna ISBN wiersza ma wartość.
  2. Komórka z tytułem lub autorem w wierszu jest pusta.

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

Pozostało tylko dodanie informacji bookData do arkusza kalkulacyjnego. Jest jednak pewien haczyk. Niestety publiczne interfejsy API, takie jak Open Library Book API, czasami nie zawierają żądanych informacji lub mają inne problemy, które uniemożliwiają ich udostępnienie. Jeśli założysz, że każde żądanie do interfejsu API zakończy się powodzeniem, Twój kod nie będzie wystarczająco odporny na nieoczekiwane błędy.

Aby mieć pewność, że kod poradzi sobie z błędami interfejsu API, musi on sprawdzać, czy odpowiedź interfejsu API jest prawidłowa, zanim spróbuje jej użyć. Gdy kod ma wartość bookData, przeprowadza prosty test, aby sprawdzić, czy istnieją wartości bookData i bookData.details, zanim spróbuje je odczytać. Jeśli brakuje jednego z tych elementów, oznacza to, że interfejs API nie zawierał potrzebnych Ci danych. W tym przypadku polecenie continue informuje kod, aby pominąć ten wiersz. Nie możesz wypełnić brakujących komórek, ale przynajmniej skrypt nie ulegnie awarii.

3. Zapisz zaktualizowane informacje z powrotem w arkuszu

Ostatnia część kodu zawiera podobne sprawdzenia, które weryfikują, czy interfejs API zwrócił informacje o tytule i autorze. Kod aktualizuje tablicę bookValues tylko wtedy, gdy oryginalna komórka tytułu lub autora jest pusta, a interfejs API zwrócił wartość, którą można w niej umieścić.

Pętla kończy się po sprawdzeniu wszystkich wierszy w arkuszu. Ostatnim krokiem jest zapisanie zaktualizowanej tablicy bookValues z powrotem w arkuszu za pomocą funkcji Range.setValues(values).

Wyniki

Możesz teraz dokończyć czyszczenie danych książki. Wykonaj te czynności:

  1. Jeśli jeszcze tego nie zrobiono, zaznacz w arkuszu zakres A2:A15 i wybierz Lista książek > Oddziel tytuł i autora przy pierwszym przecinku, aby rozwiązać problemy z przecinkami.
  2. Jeśli jeszcze tego nie zrobiono, zaznacz w arkuszu zakres A2:A15 i kliknij Lista książek > Oddziel tytuł i autora przy ostatnim słowie „by”, aby rozwiązać problemy ze słowem „by”.
  3. Aby wypełnić wszystkie pozostałe komórki, kliknij Lista książek > Wypełnij puste komórki tytułów i autorów:

826675a3437adbdb.gif

13. Podsumowanie

Gratulujemy ukończenia tego ćwiczenia. Wiesz już, jak tworzyć niestandardowe menu, aby aktywować różne części kodu Apps Script. Wiesz już też, jak importować dane do Arkuszy Google za pomocą usług Apps Script i publicznych interfejsów API. Jest to typowa operacja przetwarzania arkuszy kalkulacyjnych, a Apps Script umożliwia importowanie danych z wielu różnych źródeł. Na koniec zobaczysz, jak za pomocą usług Apps Script i JavaScriptu odczytywać, przetwarzać i wstawiać dane arkusza kalkulacyjnego.

Czy ten codelab był przydatny?

Tak Nie

Czego się dowiedziałeś

  • Jak importować dane z arkusza kalkulacyjnego Google.
  • Jak utworzyć menu niestandardowe w funkcji onOpen().
  • Jak analizować i przetwarzać wartości danych w postaci ciągów znaków.
  • Jak wywoływać publiczne interfejsy API za pomocą usługi pobierania adresów URL.
  • Jak analizować dane obiektu JSON pobrane ze źródła publicznego interfejsu API.

Co dalej?

Kolejne ćwiczenie z tej playlisty zawiera więcej informacji o tym, jak formatować dane w arkuszu kalkulacyjnym.

Kolejne ćwiczenia z programowania znajdziesz w sekcji Formatowanie danych.