Halaman ini diterjemahkan oleh Cloud Translation API.

Dasar-Dasar Apps Script dengan Google Spreadsheet #3: Bekerja dengan Data

1. Pengantar

Selamat datang di bagian ketiga playlist codelab Fundamentals of Apps Script with Google Sheets.

Dengan menyelesaikan codelab ini, Anda dapat mempelajari cara menggunakan manipulasi data, menu kustom, dan pengambilan data API publik di Apps Script untuk meningkatkan pengalaman Spreadsheet Anda. Anda akan terus bekerja dengan class SpreadsheetApp, Spreadsheet, Sheet, dan Range yang diperkenalkan di codelab sebelumnya dalam playlist ini.

Yang akan Anda pelajari

Cara mengimpor data dari spreadsheet pribadi atau bersama di Drive.
Cara membuat menu kustom dengan fungsi onOpen().
Cara mengurai dan memanipulasi nilai data string di sel Google Spreadsheet.
Cara menarik dan memanipulasi data objek JSON dari sumber API publik.

Sebelum memulai

Ini adalah codelab ketiga dalam playlist Dasar-Dasar Apps Script dengan Google Spreadsheet. Sebelum memulai codelab ini, pastikan untuk menyelesaikan codelab sebelumnya:

Yang Anda butuhkan

Pemahaman tentang topik dasar Apps Script yang dibahas dalam codelab sebelumnya di playlist ini.
Pemahaman dasar tentang editor Apps Script
Pemahaman dasar tentang Google Spreadsheet
Kemampuan untuk membaca Spreadsheet dengan Notasi A1
Pemahaman dasar tentang JavaScript dan class String-nya

2. Siapkan

Latihan dalam codelab ini memerlukan spreadsheet untuk dikerjakan. Ikuti langkah-langkah berikut untuk membuat spreadsheet yang akan digunakan dalam latihan ini:

Buat spreadsheet di Google Drive Anda. Anda dapat melakukannya dari antarmuka Drive dengan memilih Baru > Google Spreadsheet. Tindakan ini akan membuat dan membuka spreadsheet baru Anda. File akan disimpan ke folder Drive Anda.
Klik judul spreadsheet dan ubah dari "Spreadsheet tanpa judul" menjadi "Manipulasi Data dan Menu Kustom". Spreadsheet Anda akan terlihat seperti ini:

Untuk membuka editor skrip, klik Ekstensi> Apps Script
Klik judul project Apps Script dan ubah dari "Untitled Project" menjadi "Data Manipulation and Custom Menus". Klik Ganti nama untuk menyimpan perubahan judul.

Dengan spreadsheet dan project kosong, Anda siap memulai lab. Lanjutkan ke bagian berikutnya untuk mulai mempelajari menu kustom.

3. Ringkasan: Mengimpor data dengan item menu kustom

Apps Script memberi Anda kemampuan untuk menentukan menu kustom yang dapat muncul di Google Spreadsheet. Anda juga dapat menggunakan menu kustom di Google Dokumen, Google Slide, dan Google Formulir. Saat menentukan item menu kustom, Anda membuat label teks dan menghubungkannya ke fungsi Apps Script di project skrip Anda. Kemudian, Anda dapat menambahkan menu ke UI agar muncul di Google Spreadsheet:

Saat pengguna mengklik item menu kustom, fungsi Apps Script yang Anda kaitkan dengannya akan dijalankan. Ini adalah cara cepat untuk menjalankan fungsi Apps Script tanpa harus membuka editor skrip. Hal ini juga memungkinkan pengguna lain spreadsheet untuk menjalankan kode Anda tanpa harus mengetahui apa pun tentang cara kerja kode atau Apps Script. Bagi mereka, ini hanyalah item menu lain.

Item menu kustom ditentukan dalam fungsi pemicu sederhana onOpen() , yang akan Anda pelajari di bagian berikutnya.

4. Fungsi onOpen()

Pemicu sederhana di Apps Script menyediakan cara untuk menjalankan kode Apps Script tertentu sebagai respons terhadap kondisi atau peristiwa tertentu. Saat membuat pemicu, Anda menentukan peristiwa yang menyebabkan pemicu diaktifkan dan menyediakan fungsi Apps Script yang berjalan untuk peristiwa tersebut.

onOpen() adalah contoh pemicu sederhana. Fungsi ini mudah disiapkan—Anda hanya perlu menulis fungsi Apps Script bernama onOpen() dan Apps Script akan menjalankannya setiap kali spreadsheet terkait dibuka atau dimuat ulang:

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

Penerapan

Mari kita buat menu kustom.

Ganti kode di project skrip Anda dengan kode berikut:

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

Simpan project skrip Anda.

Peninjauan kode

Mari kita tinjau kode ini untuk memahami cara kerjanya. Di onOpen(), baris pertama menggunakan metode getUi() untuk mendapatkan objek Ui yang merepresentasikan antarmuka pengguna spreadsheet aktif yang terikat dengan skrip ini.

Tiga baris berikutnya membuat menu (Book-list), menambahkan item menu (Load Book-list) ke menu tersebut, lalu menambahkan menu ke antarmuka spreadsheet. Hal ini dilakukan dengan metode createMenu(caption), addItem(caption, functionName), dan addToUi().

Metode addItem(caption, functionName) membuat koneksi antara label item menu dan fungsi Apps Script yang berjalan saat item menu dipilih. Dalam hal ini, memilih item menu Load Book-list akan menyebabkan Spreadsheet mencoba menjalankan fungsi loadBookList() (yang belum ada).

Hasil

Jalankan fungsi ini sekarang untuk melihat cara kerjanya:

Di Google Spreadsheet, muat ulang spreadsheet Anda. Catatan: tindakan ini biasanya akan menutup tab dengan editor skrip Anda.
Buka kembali editor skrip dengan memilih Alat > Editor skrip.

Setelah spreadsheet dimuat ulang, menu Book-list baru akan muncul di menu bar:

Dengan mengklik Book-list, Anda dapat melihat menu yang dihasilkan:

Bagian berikutnya membuat kode untuk fungsi loadBookList() dan memperkenalkan salah satu cara Anda dapat berinteraksi dengan data di Apps Script: membaca spreadsheet lain.

5. Mengimpor data spreadsheet

Setelah membuat menu kustom, Anda dapat membuat fungsi yang dapat dijalankan dengan mengklik item menu.

Saat ini, menu kustom Book-list memiliki satu item menu: Load Book-list. Fungsi yang dipanggil saat Anda memilih item menu Load Book-list, loadBookList(), tidak ada dalam skrip Anda, sehingga memilih Book-list > Load Book-list akan memunculkan error:

Anda dapat memperbaiki error ini dengan menerapkan fungsi loadBookList().

Penerapan

Anda ingin item menu baru mengisi spreadsheet dengan data untuk dikerjakan, jadi Anda akan menerapkan loadBookList() untuk membaca data buku dari spreadsheet lain dan menyalinnya ke spreadsheet ini:

Tambahkan kode berikut ke skrip Anda di bagian 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);
}

Simpan project skrip Anda.

Peninjauan kode

Jadi, bagaimana cara kerja fungsi ini? Fungsi loadBookList() menggunakan metode terutama dari class Spreadsheet, Sheet, dan Range yang diperkenalkan di codelab sebelumnya. Dengan mempertimbangkan konsep ini, Anda dapat menguraikan kode loadBookList() menjadi empat bagian berikut:

1: Identifikasi sheet tujuan

Baris pertama menggunakan SpreadsheetApp.getActiveSheet() untuk mendapatkan referensi ke objek sheet saat ini dan menyimpannya dalam variabel sheet. Ini adalah sheet tempat data akan disalin.

2: Identifikasi data sumber

Beberapa baris berikutnya menetapkan empat variabel yang merujuk ke data sumber yang Anda ambil:

bookSS menyimpan referensi ke spreadsheet tempat kode membaca data. Kode menemukan spreadsheet berdasarkan ID spreadsheet. Dalam contoh ini, kami memberikan ID spreadsheet sumber yang akan dibaca, dan membuka spreadsheet menggunakan metode SpreadsheetApp.openById(id).
bookSheet menyimpan referensi ke sheet dalam bookSS yang berisi data yang Anda inginkan. Kode mengidentifikasi sheet yang akan dibaca berdasarkan namanya, codelab-book-list.
bookRange menyimpan referensi ke rentang data di bookSheet. Metode Sheet.getDataRange() menampilkan rentang yang berisi semua sel yang tidak kosong dalam sheet. Cara ini adalah cara mudah untuk memastikan Anda mendapatkan rentang yang mencakup semua data dalam sheet tanpa menyertakan baris dan kolom kosong.
bookListValues adalah array 2D yang berisi semua nilai yang diambil dari sel di bookRange. Metode Range.getValues() menghasilkan array ini dengan membaca data dari sheet sumber.

3: Salin data dari sumber ke tujuan

Bagian kode berikutnya menyalin data bookListValues ke sheet, lalu mengganti nama sheet:

Sheet.getRange(row, column, numRows, numColumns) digunakan untuk mengidentifikasi tempat menyalin data di sheet.
Metode Range.getHeight() dan Range.getWidth() digunakan untuk mengukur ukuran data dan menentukan rentang tujuan dengan dimensi yang sama.
Range.setValues(values) menyalin array 2D bookListValues ke rentang tujuan, menimpa data apa pun yang sudah ada di sana.

4: Memformat sheet tujuan

Sheet.setName(name) digunakan untuk mengubah nama sheet tujuan menjadi Book-list. Baris terakhir dalam fungsi menggunakan Sheet.autoResizeColumns(startColumn, numColumns) untuk mengubah ukuran tiga kolom pertama di sheet tujuan, sehingga Anda dapat membaca data baru dengan lebih mudah.

Hasil

Anda dapat melihat cara kerja fungsi ini. Di Google Spreadsheet, pilih Book-list > Load book-list untuk menjalankan fungsi guna mengisi spreadsheet Anda:

Sekarang Anda memiliki spreadsheet yang berisi daftar judul buku, penulis, dan nomor ISBN 13 digit. Di bagian berikutnya, Anda akan mempelajari cara mengubah dan memperbarui data dalam daftar buku ini menggunakan manipulasi string dan menu kustom.

6. Ringkasan: Membersihkan data spreadsheet

Sekarang Anda memiliki informasi buku di spreadsheet. Setiap baris merujuk pada buku tertentu, yang mencantumkan judul, penulis, dan nomor ISBN dalam kolom terpisah. Namun, Anda juga dapat melihat beberapa masalah pada data mentah ini:

Untuk beberapa baris, judul dan penulis ditempatkan bersama di kolom judul, yang ditautkan dengan koma atau string " oleh ".
Beberapa baris tidak mencantumkan judul atau penulis buku.

Di bagian berikutnya, Anda akan memperbaiki masalah ini dengan membersihkan data. Untuk masalah pertama, Anda akan membuat fungsi yang membaca kolom judul dan memisahkan teks setiap kali ditemukan koma atau pemisah " by ", lalu menempatkan substring penulis dan judul yang sesuai di kolom yang benar. Untuk masalah kedua, Anda akan menulis kode yang secara otomatis mencari informasi buku yang tidak ada menggunakan API eksternal, dan menambahkan informasi tersebut ke spreadsheet Anda.

7. Menambahkan item menu

Anda harus membuat tiga item menu untuk mengontrol operasi pembersihan data yang akan Anda terapkan.

Penerapan

Mari kita perbarui onOpen() untuk menyertakan item menu tambahan yang Anda perlukan. Lakukan hal berikut:

Di project skrip Anda, perbarui kode onOpen() agar cocok dengan kode berikut:

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

Simpan project skrip Anda.
Di editor skrip, pilih onOpen dari daftar fungsi, lalu klik Run. Tindakan ini akan menjalankan onOpen() untuk membangun kembali menu spreadsheet sehingga Anda tidak perlu memuat ulang spreadsheet.

Dalam kode baru ini, metode Menu.addSeparator() membuat pemisah horizontal di menu untuk menjaga agar grup item menu terkait tetap teratur secara visual. Item menu baru kemudian ditambahkan di bawahnya, dengan label Separate title/author at first comma, Separate title/author at last "by", dan Fill in blank titles and author cells.

Hasil

Di spreadsheet Anda, klik menu Book-list untuk melihat item menu baru:

Mengklik item baru ini akan menyebabkan error karena Anda belum menerapkan fungsi yang sesuai, jadi mari kita lakukan.

8. Membagi teks pada pembatas koma

Set data yang Anda impor ke spreadsheet memiliki beberapa sel yang berisi gabungan penulis dan judul yang salah dalam satu sel menggunakan koma:

Memisahkan string teks ke dalam kolom terpisah adalah tugas spreadsheet yang umum. Google Spreadsheet menyediakan fungsi SPLIT() yang membagi string ke dalam kolom. Namun, set data sering kali memiliki masalah yang tidak dapat diselesaikan dengan mudah menggunakan fungsi bawaan Spreadsheet. Dalam kasus ini, Anda dapat menulis kode Apps Script untuk melakukan operasi kompleks yang diperlukan untuk membersihkan dan mengatur data Anda.

Mulai bersihkan data Anda dengan terlebih dahulu menerapkan fungsi yang disebut splitAtFirstComma() yang membagi penulis dan judul ke dalam sel masing-masing saat koma ditemukan.

Fungsi splitAtFirstComma() harus melakukan langkah-langkah berikut:

Mendapatkan rentang yang merepresentasikan sel yang saat ini dipilih.
Periksa apakah sel dalam rentang memiliki koma.
Jika koma ditemukan, pisahkan string menjadi dua (dan hanya dua) substring di lokasi koma pertama. Untuk mempermudah, Anda dapat mengasumsikan bahwa setiap koma menunjukkan pola string "[authors], [title]". Anda juga dapat mengasumsikan bahwa jika beberapa koma muncul dalam sel, string tersebut harus dibagi pada koma pertama.
Tetapkan substring sebagai konten baru dari sel judul dan penulis masing-masing.

Penerapan

Untuk menerapkan langkah-langkah ini, Anda akan menggunakan metode Spreadsheet service yang sama dengan yang telah Anda gunakan sebelumnya, tetapi Anda juga perlu menggunakan JavaScript untuk memanipulasi data string. Lakukan langkah-langkah berikut:

Di editor Apps Script, tambahkan fungsi berikut ke akhir project skrip Anda:

/**
 * 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);
}

Simpan project skrip Anda.

Peninjauan kode

Mari kita tinjau kode baru yang terdiri dari tiga bagian utama:

1: Ambil nilai judul yang ditandai

Tiga baris pertama menetapkan tiga variabel yang merujuk pada data saat ini dalam sheet:

activeRange mewakili rentang yang saat ini ditandai pengguna saat fungsi splitAtFirstComma() dipanggil. Untuk menyederhanakan latihan ini, kita dapat mengasumsikan pengguna hanya melakukannya saat menandai sel di kolom A.
titleAuthorRange mewakili rentang baru yang mencakup sel yang sama dengan activeRange, tetapi juga menyertakan satu kolom lagi di sebelah kanan. titleAuthorRange dibuat menggunakan metode Range.offset(rowOffset, columnOffset, numRows, numColumns). Kode memerlukan rentang yang diperluas ini karena memerlukan tempat untuk menempatkan penulis yang ditemukan di kolom judul.
titleAuthorValues adalah array data 2D yang diekstrak dari titleAuthorRange menggunakan Range.getValues().

2: Periksa setiap judul dan pisahkan pada pembatas koma pertama yang ditemukan

Bagian berikutnya memeriksa nilai dalam titleAuthorValues untuk menemukan koma. JavaScript For Loop digunakan untuk memeriksa semua nilai di kolom pertama titleAuthorValues. Saat substring koma ditemukan (", ") menggunakan metode JavaScript String indexOf(), kode akan melakukan hal berikut:

Nilai string sel disalin ke variabel titlesAndAuthors.
Lokasi koma ditentukan menggunakan metode JavaScript String indexOf().
Metode JavaScript String slice() dipanggil dua kali untuk mendapatkan substring sebelum pembatas koma dan substring setelah pembatas.
Substring disalin kembali ke array 2D titleAuthorValues, menggantikan nilai yang ada di posisi tersebut. Karena kita mengasumsikan pola "[authors], [title]", urutan dua substring dibalik untuk menempatkan judul di kolom pertama dan penulis di kolom kedua.

Catatan: Jika kode tidak menemukan koma, kode akan membiarkan data di baris tidak berubah.

3: Salin kembali nilai baru ke dalam spreadsheet

Setelah semua nilai sel judul diperiksa, array 2D titleAuthorValues yang diperbarui disalin kembali ke spreadsheet menggunakan metode Range.setValues(values).

Hasil

Sekarang Anda dapat melihat efek fungsi splitAtFirstComma(). Coba jalankan dengan memilih item menu Separate title/author at first comma setelah memilih...

...satu sel:

...atau beberapa sel:

Sekarang Anda telah membuat fungsi Apps Script yang memproses data Spreadsheet. Selanjutnya, Anda akan menerapkan fungsi pemisah kedua.

9. Membagi teks pada pembatas "by"

Dengan melihat data asli, Anda dapat melihat masalah lain. Sama seperti beberapa format data yang mencantumkan judul dan penulis dalam satu sel sebagai "[penulis], [judul]", sel lain memformat penulis dan judul sebagai "[judul] oleh [penulis]":

Penerapan

Anda dapat mengatasi masalah ini menggunakan teknik yang sama dari bagian terakhir, dengan membuat fungsi bernama splitAtLastBy(). Fungsi ini memiliki tugas yang serupa dengan splitAtFirstComma()—satu-satunya perbedaan nyata adalah fungsi ini menelusuri pola teks yang sedikit berbeda. Terapkan fungsi ini dengan melakukan hal berikut:

Di editor Apps Script, tambahkan fungsi berikut ke akhir project skrip Anda:

/** 
 * 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);
}

Simpan project skrip Anda.

Peninjauan kode

Ada beberapa perbedaan utama antara kode ini dan splitAtFirstComma():

Substring " by " digunakan sebagai pembatas string, bukan ", ".
Di sini, metode JavaScript String.lastIndexOf(substring) digunakan, bukan String.indexOf(substring). Artinya, jika ada beberapa substring " by " dalam string awal, semua substring " by " kecuali yang terakhir dianggap sebagai bagian dari judul.
Setelah memisahkan string, sub-string pertama ditetapkan sebagai judul dan sub-string kedua sebagai penulis (ini adalah urutan yang berlawanan dari splitAtFirstComma()).

Hasil

Sekarang Anda dapat melihat efek fungsi splitAtLastBy(). Coba jalankan dengan memilih item menu Separate title/author at last "by" setelah memilih...

...satu sel:

...atau beberapa sel:

Anda telah menyelesaikan bagian codelab ini. Sekarang Anda dapat menggunakan Apps Script untuk membaca dan mengubah data string dalam sheet, serta menggunakan menu kustom untuk menjalankan berbagai perintah Apps Script.

Di bagian berikutnya, Anda akan mempelajari cara lebih meningkatkan kualitas set data ini dengan mengisi sel kosong menggunakan data yang diambil dari API publik.

10. Ringkasan: Mendapatkan data dari API publik

Sejauh ini Anda telah menyempurnakan set data untuk memperbaiki beberapa masalah pemformatan judul dan penulis, tetapi set data masih kehilangan beberapa informasi, yang ditandai dalam sel di bawah:

Anda tidak bisa mendapatkan data yang hilang dengan menggunakan operasi string pada data yang saat ini Anda miliki. Sebagai gantinya, Anda harus mendapatkan data yang hilang dari sumber lain. Anda dapat melakukannya di Apps Script dengan meminta informasi dari API eksternal yang dapat memberikan data tambahan.

API adalah antarmuka pemrograman aplikasi. Ini adalah istilah umum, tetapi pada dasarnya merupakan layanan yang dapat dipanggil oleh program dan skrip Anda untuk meminta informasi atau melakukan tindakan tertentu. Di bagian ini, Anda memanggil API yang tersedia untuk publik guna meminta informasi buku yang dapat Anda masukkan ke dalam sel kosong di spreadsheet.

Bagian ini mengajarkan cara:

Meminta data buku dari sumber API eksternal.
Ekstrak informasi judul dan penulis dari data yang ditampilkan dan tulis ke spreadsheet Anda.

11. Mengambil data eksternal dengan UrlFetch

Sebelum mempelajari kode yang berfungsi langsung dengan spreadsheet Anda, Anda dapat mempelajari cara menggunakan API eksternal di Apps Script dengan membuat fungsi bantuan khusus untuk meminta informasi buku dari Open Library API publik.

Fungsi helper kami, fetchBookData_(ISBN), menggunakan nomor ISBN 13 digit buku sebagai parameter dan menampilkan data tentang buku tersebut. Fungsi ini terhubung ke dan mengambil informasi dari Open Library API, lalu menguraikan objek JSON yang ditampilkan.

Penerapan

Terapkan fungsi bantuan ini dengan melakukan hal berikut:

Di editor Apps Script, tambahkan kode berikut di akhir skrip Anda:

/**
 * 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];
}

Simpan project skrip Anda.

Peninjauan kode

Kode ini dibagi menjadi dua bagian utama:

1: Permintaan API

Pada dua baris pertama, fetchBookData_(ISBN) terhubung ke Open Library API publik menggunakan endpoint URL API dan Layanan Pengambilan URL Apps Script.

Variabel url hanyalah string URL, seperti alamat web. URL ini mengarah ke lokasi di server Open Library. API ini juga mencakup tiga parameter (bibkeys, jscmd, dan format) yang memberi tahu server Open Library informasi yang Anda minta dan cara menyusun respons. Dalam hal ini, Anda memberikan nomor ISBN buku dan meminta informasi mendetail ditampilkan dalam format JSON.

Setelah Anda membuat string URL, kode akan mengirimkan permintaan ke lokasi dan menerima respons. Hal ini dilakukan dengan metode UrlFetchApp.fetch(url, params). Tindakan ini mengirim permintaan informasi ke URL eksternal yang Anda berikan dan menyimpan respons yang dihasilkan dalam variabel response. Selain URL, kode ini menetapkan parameter opsional muteHttpExceptions ke true. Setelan ini berarti kode Anda tidak akan berhenti jika permintaan menghasilkan error API. Sebagai gantinya, respons error akan ditampilkan.

Permintaan menampilkan objek HTTPResponse yang disimpan dalam variabel response. Respons HTTP mencakup kode respons, header HTTP, dan konten respons utama. Informasi yang menarik di sini adalah konten JSON utama, sehingga kode harus mengekstraknya, lalu mengurai JSON untuk menemukan dan menampilkan informasi yang diinginkan.

2: Mengurai respons API dan menampilkan informasi yang diinginkan

Dalam tiga baris kode terakhir, metode HTTPResponse.getContentText() menampilkan konten utama respons sebagai string. String ini dalam format JSON, tetapi Open Library API menentukan konten dan format yang tepat. Metode JSON.parse(jsonString) mengonversi string JSON menjadi objek JavaScript sehingga berbagai bagian data dapat diekstrak dengan mudah. Terakhir, fungsi ini menampilkan data yang sesuai dengan nomor ISBN buku.

Hasil

Setelah menerapkan fetchBookData_(ISBN), fungsi lain dalam kode Anda dapat menemukan informasi untuk buku apa pun menggunakan nomor ISBN-nya. Anda akan menggunakan fungsi ini untuk membantu mengisi sel dalam spreadsheet.

12. Menulis data API ke spreadsheet

Sekarang Anda dapat menerapkan fungsi fillInTheBlanks() yang melakukan hal berikut:

Identifikasi data judul dan penulis yang tidak ada dalam rentang data aktif.
Ambil data yang hilang dari buku tertentu dengan memanggil Open Library API menggunakan metode helper fetchBookData_(ISBN).
Perbarui nilai judul atau penulis yang tidak ada di sel masing-masing.

Penerapan

Terapkan fungsi baru ini dengan melakukan hal berikut:

Di editor Apps Script, tambahkan kode berikut di akhir project skrip Anda:

/**
 * 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);
}

Simpan project skrip Anda.

Peninjauan kode

Kode ini dibagi menjadi tiga bagian:

1: Baca informasi buku yang ada

Tiga baris pertama fungsi menentukan konstanta untuk membantu membuat kode lebih mudah dibaca. Pada dua baris berikutnya, variabel bookValues digunakan untuk mempertahankan salinan lokal informasi buku sheet. Kode akan membaca informasi dari bookValues, menggunakan API untuk mengisi informasi yang tidak ada, dan menulis kembali nilai ini ke spreadsheet.

2: Ambil informasi yang tidak ada menggunakan fungsi helper

Kode ini mengulang setiap baris di bookValues untuk menemukan judul atau penulis yang tidak ada. Untuk mengurangi jumlah panggilan API sekaligus meningkatkan efisiensi, kode hanya memanggil API jika hal berikut benar:

Kolom ISBN baris memiliki nilai.
Sel judul atau penulis di baris kosong.

Jika kondisinya benar, kode akan memanggil API menggunakan fungsi helper fetchBookData_(isbn) yang Anda terapkan sebelumnya, dan menyimpan hasilnya dalam variabel bookData. Sekarang, kolom tersebut akan berisi informasi yang hilang yang ingin Anda masukkan ke dalam sheet.

Satu-satunya tugas yang tersisa adalah menambahkan informasi bookData ke spreadsheet kita. Namun, ada satu hal yang perlu diperhatikan. Sayangnya, API publik seperti Open Library Book API terkadang tidak memiliki informasi yang Anda minta, atau terkadang mungkin mengalami masalah lain yang mencegahnya memberikan informasi. Jika Anda mengasumsikan setiap permintaan API akan berhasil, kode Anda tidak akan cukup kuat untuk menangani error yang tidak terduga.

Untuk memastikan kode Anda dapat menangani error API, kode harus memeriksa apakah respons API valid sebelum mencoba menggunakannya. Setelah memiliki bookData, kode akan melakukan pemeriksaan sederhana untuk memverifikasi keberadaan bookData dan bookData.details sebelum mencoba membaca dari keduanya. Jika salah satunya tidak ada, berarti API tidak memiliki data yang Anda inginkan. Dalam hal ini, perintah continue memberi tahu kode untuk melewati baris tersebut—Anda tidak dapat mengisi sel yang kosong, tetapi setidaknya skrip Anda tidak akan error.

3: Menulis kembali informasi yang diperbarui ke dalam spreadsheet

Bagian terakhir kode memiliki pemeriksaan serupa untuk memverifikasi informasi judul dan penulis yang ditampilkan API. Kode hanya memperbarui array bookValues jika sel judul atau penulis asli kosong dan API menampilkan nilai yang dapat Anda tempatkan di sana.

Loop akan keluar setelah semua baris dalam sheet diperiksa. Langkah terakhir adalah menulis kembali array bookValues yang kini telah diperbarui ke spreadsheet menggunakan Range.setValues(values).

Hasil

Sekarang Anda dapat menyelesaikan pembersihan data buku. Lakukan hal berikut:

Jika belum, tandai rentang A2:A15 di spreadsheet Anda, lalu pilih Book-list > Separate title/author at first comma untuk mengatasi masalah koma.
Jika belum, tandai rentang A2:A15 di spreadsheet Anda, lalu pilih Daftar buku > Pisahkan judul/penulis di "oleh" terakhir untuk mengatasi masalah "oleh".
Untuk mengisi semua sel yang tersisa, pilih Daftar buku > Isi sel judul dan penulis yang kosong:

13. Kesimpulan

Selamat, Anda telah menyelesaikan codelab ini. Anda telah mempelajari cara membuat menu kustom untuk mengaktifkan berbagai bagian kode Apps Script. Anda juga telah melihat cara mengimpor data ke Google Spreadsheet menggunakan layanan Apps Script dan API publik. Ini adalah operasi umum dalam pemrosesan spreadsheet, dan Apps Script memungkinkan Anda mengimpor data dari berbagai sumber. Terakhir, Anda telah melihat cara menggunakan layanan Apps Script dan JavaScript untuk membaca, memproses, dan memasukkan data spreadsheet.

Apakah codelab ini bermanfaat bagi Anda?

Tidak

Yang telah Anda pelajari

Cara mengimpor data dari spreadsheet Google.
Cara membuat menu kustom di fungsi onOpen().
Cara mengurai dan memanipulasi nilai data string.
Cara memanggil API publik menggunakan Layanan URL-Fetch.
Cara mengurai data objek JSON yang diambil dari sumber API publik.

Konsep utama:

onOpen() adalah pemicu sederhana yang digunakan untuk membuat item menu. Pemicu sederhana memiliki berbagai batasan yang harus Anda patuhi.
Saat membaca atau menulis data sel spreadsheet, sebaiknya baca atau tulis seluruh rentang sekaligus dengan Range.getValues() atau Range.setValues(values). Jangan membaca atau menyetel setiap sel satu per satu, karena akan lebih lambat.
Nama fungsi Apps Script yang diakhiri dengan _ (garis bawah) dianggap pribadi. Skrip lain tidak dapat memanggil fungsi ini jika ada di library, atau oleh klien melalui komunikasi server-klien. Jika Anda tahu bahwa suatu fungsi hanya akan digunakan oleh skrip saat ini, sebaiknya akhiri nama fungsi dengan _.
Saat menggunakan data yang ditampilkan oleh API eksternal, validasi data sebelum mencoba menggunakannya. Hal ini mengurangi error dalam kode Anda jika API menampilkan hasil yang tidak terduga.

Istilah utama:

Aktif (status): Menunjukkan bahwa spreadsheet, sheet, rentang, atau sel yang ditentukan adalah yang sedang dilihat atau ditandai oleh pengguna spreadsheet.
Sel aktif: Sel yang disorot dalam sheet aktif dengan fokus kursor.
Rentang aktif: Sekelompok satu atau beberapa sel yang saat ini ditandai dalam sheet aktif.
API: Application programming interface adalah layanan yang dapat dipanggil oleh program atau skrip untuk meminta informasi atau melakukan tindakan tertentu.
Otorisasi: Proses pemberian izin oleh pengguna untuk mengizinkan Apps Script mengakses data pengguna di layanan Google yang relevan atau mengambil tindakan atas nama pengguna.
Skrip terikat penampung: Skrip apa pun yang terikat ke dan dibuat dari dokumen Google Workspace, seperti Spreadsheet Google atau Dokumen Google.
Fungsi helper: Fungsi, biasanya bersifat pribadi, yang dipanggil oleh fungsi lain untuk menyelesaikan sub-tugas kecil. Fungsi helper digunakan saat sub-tugas perlu diselesaikan berkali-kali di berbagai tempat.
JSON: Format file untuk menentukan objek data dan karakteristiknya dari teks.
onOpen(): Jika ditentukan dalam skrip, ini adalah pemicu sederhana yang diaktifkan setiap kali file dibuka atau dimuat ulang.
Fungsi pribadi: Fungsi yang tidak dapat dipanggil sebagai bagian dari library atau oleh klien melalui komunikasi server-klien. Fungsi pribadi Apps Script memiliki nama yang diakhiri dengan _.
Rentang**: Sekelompok satu atau beberapa sel yang berdekatan dalam sheet. Class Range** memberi Anda kemampuan untuk membaca dan memperbarui rentang dalam sheet.
Editor skrip: Editor kode dalam browser untuk Apps Script.
Pemicu sederhana: Subtipe pemicu di Apps Script dengan nama fungsi pemicu yang dicadangkan dan batasan tertentu.
Sheet**: Satu halaman spreadsheet. Terkadang digunakan untuk merujuk pada file Google Spreadsheet dalam Google Drive. Class Sheet** memungkinkan Anda mengakses dan mengubah sheet.
SpreadsheetApp: Class ini berfungsi sebagai class induk untuk layanan Spreadsheet dan memberikan titik awal untuk kode yang membaca atau memanipulasi data Google Spreadsheet.
Spreadsheet**: File Google Spreadsheet yang ada di Google Drive. Class Spreadsheet** memungkinkan Anda mengakses dan mengubah spreadsheet.
Layanan spreadsheet: Layanan Apps Script yang memungkinkan skrip membuat, mengakses, dan mengubah file Google Spreadsheet.
Pemicu: Peristiwa yang dikaitkan dengan fungsi Apps Script. Saat peristiwa (seperti spreadsheet dibuka) terjadi, pemicu akan "diaktifkan" dan otomatis menjalankan fungsi Apps Script terkait.
Layanan Pengambilan URL: Layanan Apps Script yang memungkinkan skrip terhubung ke endpoint URL untuk membuat permintaan dan menerima respons.

Langkah berikutnya

Codelab berikutnya dalam playlist ini membahas lebih mendalam cara memformat data dalam spreadsheet.

Temukan codelab berikutnya di Pemformatan data.

Dasar-Dasar Apps Script dengan Google Spreadsheet #3: Bekerja dengan Data Tetap teratur dengan koleksi Simpan dan kategorikan konten berdasarkan preferensi Anda.

1. Pengantar

Yang akan Anda pelajari

Sebelum memulai

Yang Anda butuhkan

2. Siapkan

3. Ringkasan: Mengimpor data dengan item menu kustom

4. Fungsi onOpen()

Penerapan

Peninjauan kode

Hasil

5. Mengimpor data spreadsheet

Penerapan

Peninjauan kode

1: Identifikasi sheet tujuan

2: Identifikasi data sumber

3: Salin data dari sumber ke tujuan

4: Memformat sheet tujuan

Hasil

6. Ringkasan: Membersihkan data spreadsheet

7. Menambahkan item menu

Penerapan

Hasil

8. Membagi teks pada pembatas koma

Penerapan

Peninjauan kode

1: Ambil nilai judul yang ditandai

2: Periksa setiap judul dan pisahkan pada pembatas koma pertama yang ditemukan

3: Salin kembali nilai baru ke dalam spreadsheet

Hasil

9. Membagi teks pada pembatas "by"

Penerapan

Peninjauan kode

Hasil

10. Ringkasan: Mendapatkan data dari API publik

11. Mengambil data eksternal dengan UrlFetch

Penerapan

Peninjauan kode

1: Permintaan API

2: Mengurai respons API dan menampilkan informasi yang diinginkan

Hasil

12. Menulis data API ke spreadsheet

Penerapan

Peninjauan kode

1: Baca informasi buku yang ada

2: Ambil informasi yang tidak ada menggunakan fungsi helper

3: Menulis kembali informasi yang diperbarui ke dalam spreadsheet

Hasil

13. Kesimpulan

Apakah codelab ini bermanfaat bagi Anda?

Yang telah Anda pelajari

Langkah berikutnya

Dasar-Dasar Apps Script dengan Google Spreadsheet #3: Bekerja dengan Data