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 dari playlist codelab Dasar-dasar Apps Script dengan Google Spreadsheet.

Dengan menyelesaikan codelab ini, Anda dapat mempelajari cara menggunakan manipulasi data, menu khusus, dan pengambilan data API publik di Apps Script untuk meningkatkan pengalaman Spreadsheet. Anda akan terus mengerjakan class SpreadsheetApp, Spreadsheet, Sheet, dan Range 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 Fundamentals of Apps Script dengan Google Spreadsheet. Sebelum memulai codelab ini, pastikan untuk menyelesaikan codelab sebelumnya:

Yang Anda butuhkan

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

2. Siapkan

Latihan dalam codelab ini memerlukan spreadsheet untuk berfungsi. Ikuti langkah-langkah ini untuk membuat spreadsheet guna digunakan dalam latihan ini:

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

Untuk membuka editor skrip, klik Ekstensi> Apps Script
Klik judul project Apps Script dan ubah dari "Project Tanpa Judul" menjadi "Manipulasi Data dan Menu Kustom." Klik Ganti nama untuk menyimpan perubahan judul.

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

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 khusus di Google Dokumen, Google Slide, dan Google Formulir. Saat Anda menentukan item menu khusus, Anda membuat label teks dan menghubungkannya ke fungsi Apps Script di project skrip Anda. Anda kemudian dapat menambahkan menu ke UI agar muncul di Google Spreadsheet:

Saat pengguna mengklik item menu kustom, fungsi Apps Script yang dikaitkan dengannya akan dijalankan. Ini adalah cara cepat untuk menjalankan fungsi Apps Script tanpa harus membuka editor skrip. Ini juga memungkinkan pengguna spreadsheet lain untuk mengeksekusi kode Anda tanpa harus mengetahui apa-apa tentang cara kerja atau cara kerja Apps Script. Bagi mereka, item tersebut adalah item menu yang 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 memberikan cara untuk menjalankan kode Apps Script tertentu sebagai respons terhadap kondisi atau peristiwa tertentu. Saat membuat pemicu, Anda menentukan peristiwa yang akan memicu pemicu dan memberikan fungsi Apps Script yang berjalan untuk peristiwa tersebut.

onOpen() adalah contoh pemicu sederhana. Alat tersebut mudah disiapkan—Anda hanya perlu menulis fungsi Apps Script yang 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() {
 /* ... */ 
}

Implementasi

Mari buat menu khusus.

Ganti kode dalam project skrip Anda dengan yang berikut ini:

/**
 * 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 mewakili 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 hubungan 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 bahwa perintah tersebut berfungsi:

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

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

Dengan mengklik Daftar buku, Anda dapat melihat menu yang dihasilkan:

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

5. Impor data spreadsheet

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

Saat ini, menu kustom Book-list memiliki satu item menu: Load Book-list. Fungsi ini dipanggil saat Anda memilih item menu Load Book-list, loadBookList(), tidak ada dalam skrip, jadi Anda dapat memilih Daftar buku > Muat Daftar Buku yang menampilkan error:

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

Implementasi

Anda ingin agar item menu baru mengisi spreadsheet dengan data agar dapat digunakan, sehingga Anda akan mengimplementasikan loadBookList() untuk membaca data buku dari spreadsheet lain dan menyalinnya ke spreadsheet ini:

Tambahkan kode berikut ke skrip Anda pada 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 codelab sebelumnya. Dengan konsep ini, Anda dapat memecah kode loadBookList() menjadi empat bagian berikut:

1: Mengidentifikasi 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: Mengidentifikasi data sumber

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

bookSS menyimpan referensi ke spreadsheet tempat kode tersebut membaca data. Kode akan menemukan spreadsheet berdasarkan ID spreadsheet. Dalam contoh ini, kami memberikan ID spreadsheet sumber untuk 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 untuk 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. 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 dalam bookRange. Metode Range.getValues() menghasilkan array ini dengan membaca data dari sheet sumber.

3: Menyalin data dari sumber ke tujuan

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

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

4: Format 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 Daftar buku > Muat daftar buku untuk menjalankan fungsi guna mengisi spreadsheet Anda:

Anda sekarang memiliki sheet dengan daftar judul buku, pengarang, dan nomor ISBN 13 digit. Di bagian selanjutnya, Anda akan mempelajari cara memodifikasi dan memperbarui data dalam daftar buku ini dengan menggunakan manipulasi string dan menu khusus.

6. Ringkasan: Membersihkan data spreadsheet

Kini Anda memiliki informasi buku di sheet. Setiap baris merujuk pada buku tertentu, yang mencantumkan judul, pengarang, dan nomor ISBN di kolom terpisah. Namun, Anda juga dapat melihat beberapa masalah dengan data mentah ini:

Untuk beberapa baris, judul dan pengarang ditempatkan di kolom judul bersama, dihubungkan dengan koma atau string " oleh ".
Beberapa baris tidak memiliki judul atau pengarang 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 pemisah koma atau " menurut " ditempatkan, yang menempatkan substring penulis dan judul yang sesuai dalam kolom yang benar. Untuk masalah kedua, Anda akan menulis kode yang otomatis menelusuri informasi buku yang hilang menggunakan API eksternal, dan menambahkan informasi tersebut ke sheet.

7. Menambahkan item menu

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

Implementasi

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

Dalam project skrip, update kode onOpen() agar sesuai dengan berikut ini:

/**
 * 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 Jalankan. Tindakan ini akan menjalankan onOpen() untuk membuat ulang menu spreadsheet sehingga Anda tidak perlu memuat ulang spreadsheet.

Dalam kode baru ini, metode Menu.addSeparator() membuat pemisah horizontal dalam menu untuk membuat 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, klik menu Book-list untuk melihat item menu baru:

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

8 Pisahkan teks pada pembatas koma

Set data yang Anda impor ke dalam spreadsheet memiliki beberapa sel dengan penulis dan judul digabungkan secara keliru dalam satu sel menggunakan koma:

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

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

Fungsi splitAtFirstComma() harus melakukan langkah-langkah berikut:

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

Implementasi

Untuk menerapkan langkah-langkah ini, Anda akan menggunakan metode Layanan spreadsheet yang sama dengan yang digunakan sebelumnya, tetapi Anda juga harus menggunakan JavaScript untuk memanipulasi data string. Lakukan langkah berikut:

Di editor Apps Script, tambahkan fungsi berikut ke bagian 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: Mengambil nilai judul yang ditandai

Tiga baris pertama membentuk tiga variabel yang mengacu pada data saat ini di sheet:

activeRange mewakili rentang yang saat ini disoroti pengguna saat fungsi splitAtFirstComma() dipanggil. Agar latihan ini tetap sederhana, kita dapat berasumsi bahwa pengguna hanya melakukan ini saat menandai sel di kolom A.
titleAuthorRange mewakili rentang baru yang mencakup sel yang sama seperti activeRange, tetapi juga menyertakan satu kolom lagi di sebelah kanan. titleAuthorRange dibuat menggunakan metode Range.offset(rowOffset, columnOffset, numRows, numColumns). Kode memerlukan rentang yang diluaskan ini karena memerlukan tempat untuk menempatkan penulis yang ditemukannya di kolom judul.
titleAuthorValues adalah array data 2D yang diekstrak dari titleAuthorRange menggunakan Range.getValues().

2: Memeriksa setiap judul dan memisahkannya pada pemisah koma pertama yang ditemukan

Bagian berikutnya akan memeriksa nilai dalam titleAuthorValues untuk menemukan koma. JavaScript Untuk 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 titleAuthorValues 2D, yang menimpa nilai yang ada pada posisi tersebut. Karena kita mengasumsikan "[penulis], [judul]" pola, urutan kedua 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 nilai baru kembali ke sheet

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

Hasil

Anda sekarang dapat melihat efek dari fungsi splitAtFirstComma(). Coba jalankan dengan memilih item menu Memisahkan judul/penulisan pada koma pertama setelah memilih...

...satu sel:

...atau beberapa sel:

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

9. Pisahkan teks pada "menurut&pembatas

Dengan melihat data aslinya, Anda dapat melihat masalah lain. Sama seperti beberapa format data untuk judul dan pengarang dalam satu sel sebagai "[authors], [title]", sel lainnya memformat penulis dan judul sebagai "[title] oleh [authors]":

Implementasi

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

Di editor Apps Script, tambahkan fungsi berikut ke bagian 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 String.lastIndexOf(substring) JavaScript digunakan sebagai pengganti String.indexOf(substring). Ini berarti jika ada beberapa " by " substring dalam string awal, semua kecuali &quot terakhir; by " dianggap sebagai bagian dari judul.
Setelah memisahkan string, substring pertama ditetapkan sebagai judul dan yang kedua sebagai penulis (ini adalah urutan yang berlawanan dari splitAtFirstComma()).

Hasil

Anda sekarang dapat melihat efek dari fungsi splitAtLastBy(). Coba jalankan dengan memilih item menu terpisah judul/penulisan terakhir "by" setelah memilih...

...satu sel:

...atau beberapa sel:

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

Pada bagian selanjutnya, Anda akan mempelajari lebih lanjut cara memperbaiki set data ini dengan mengisi sel kosong dengan data yang diambil dari API publik.

10. Ringkasan: Mendapatkan data dari API publik

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

Anda tidak bisa mendapatkan data yang hilang dengan menggunakan operasi string pada data yang Anda miliki saat ini. 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 adalah layanan yang dapat dipanggil oleh program dan skrip Anda untuk meminta informasi atau mengambil tindakan tertentu. Pada bagian ini, Anda memanggil API yang tersedia untuk publik guna meminta informasi buku yang dapat dimasukkan ke dalam sel kosong di sheet Anda.

Bagian ini mengajarkan cara:

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

11. Mengambil data eksternal dengan UrlFetch

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

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

Implementasi

Implementasikan fungsi bantuan ini dengan melakukan hal berikut:

Di editor Apps Script, tambahkan kode berikut ke bagian 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 hanya berupa string URL, seperti alamat web. Titik ini menunjuk ke lokasi di server Open Library. Ini juga mencakup tiga parameter (bibkeys, jscmd, dan format) yang memberi tahu server Open Library tentang informasi apa yang Anda minta dan cara menyusun respons. Dalam hal ini, Anda memberikan nomor ISBN buku dan meminta informasi mendetail agar 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). Alat ini mengirimkan permintaan informasi ke URL eksternal yang Anda berikan dan menyimpan respons yang dihasilkan dalam variabel response. Selain URL, kode tersebut menetapkan parameter opsional muteHttpExceptions ke true. Setelan ini berarti kode Anda tidak akan berhenti jika permintaan menyebabkan error API. Sebagai gantinya, respons error akan ditampilkan.

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

2: Mengurai respons API dan menampilkan informasi yang diminati

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

Hasil

Setelah mengimplementasikan 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 di spreadsheet.

12. Menulis data API ke spreadsheet

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

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

Implementasi

Implementasikan fungsi baru ini dengan melakukan hal berikut:

Di editor Apps Script, tambahkan kode berikut ke bagian 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 nilai ini kembali ke spreadsheet.

2: Mengambil informasi yang tidak ada menggunakan fungsi bantuan

Kode di-loop setiap baris di bookValues untuk menemukan judul atau pengarang yang hilang. Untuk mengurangi jumlah panggilan API sembari meningkatkan efisiensi, kode hanya memanggil API jika hal berikut benar:

Kolom ISBN baris memiliki nilai.
Judul atau sel penulis di baris kosong.

Jika kondisi benar, kode akan memanggil API menggunakan fungsi bantuan fetchBookData_(isbn) yang Anda implementasikan sebelumnya, dan menyimpan hasilnya di variabel bookData. Sekarang akan ada informasi yang tidak ada yang ingin Anda masukkan ke sheet.

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

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

3: Menulis kembali informasi yang diperbarui ke sheet

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

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

Hasil

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

Jika belum, tandai rentang A2:A15 di sheet, lalu pilih Daftar buku > Pisahkan judul/penulis di koma pertama untuk mengatasi masalah koma.
Jika belum, sorot rentang A2:A15 di spreadsheet Anda, dan pilih Daftar buku > Pisahkan judul/penulis pada akhirnya "menurut" untuk membersihkan masalah "menurut".
Untuk mengisi semua sel yang tersisa, pilih Daftar buku > Isi judul kosong dan sel pengarang:

13. Kesimpulan

Selamat, Anda telah menyelesaikan codelab ini. Anda telah mempelajari cara membuat menu khusus untuk mengaktifkan bagian lain dari 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 menyisipkan 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 Pengambilan URL.
Cara menguraikan 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, praktik terbaiknya adalah membaca atau menulis 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 sebagai pribadi. Skrip lain tidak dapat memanggil fungsi ini saat ada di library, atau oleh klien melalui komunikasi server-klien. Jika Anda mengetahui fungsi hanya akan digunakan oleh skrip saat ini, praktik terbaiknya adalah mengakhiri 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 spreadsheet, sheet, rentang, atau sel yang ditentukan adalah spreadsheet yang sedang dilihat atau disoroti oleh pengguna spreadsheet.
Sel aktif: Satu sel yang ditandai dalam sheet aktif dengan fokus kursor.
Rentang aktif: Grup satu atau beberapa sel yang saat ini ditandai dalam sheet aktif.
API: Antarmuka pemrograman aplikasi adalah layanan yang dapat dipanggil oleh program atau skrip untuk meminta informasi atau mengambil tindakan tertentu.
Otorisasi: Proses pengguna memberikan izin untuk mengizinkan Apps Script mengakses data pengguna di layanan Google yang relevan atau melakukan tindakan atas nama pengguna.
Skrip yang terikat dengan penampung: Skrip apa pun yang terikat dan dibuat dari dokumen Google Workspace, seperti Google Spreadsheet atau Google Dokumen.
Fungsi bantuan: Fungsi, biasanya bersifat pribadi, yang dipanggil oleh fungsi lain untuk menyelesaikan subtugas kecil. Fungsi bantuan digunakan saat subtugas perlu diselesaikan beberapa kali di tempat yang berbeda.
JSON: Format file untuk menentukan objek data dan karakteristiknya dari teks.
onOpen(): Jika ditetapkan 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**: Grup dari 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: Subjenis pemicu di Apps Script dengan nama fungsi pemicu yang dicadangkan dan batasan tertentu.
Sheet**: Satu halaman spreadsheet. Terkadang digunakan untuk merujuk ke 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 menyediakan titik awal untuk kode yang membaca atau memanipulasi data Google Spreadsheet.
Spreadsheet**: File Google Spreadsheet yang berada 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 dikunci ke fungsi Apps Script. Saat peristiwa (seperti spreadsheet dibuka) terjadi, pemicu "fire" dan otomatis menjalankan fungsi Apps Script yang terkait.
Layanan Pengambilan URL: Layanan Apps Script yang memungkinkan skrip terhubung ke endpoint URL untuk membuat permintaan dan menerima respons.

Apa selanjutnya

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

Temukan codelab berikutnya di Format data.