Dasar-Dasar Apps Script dengan Google Spreadsheet #4: Pemformatan Data

1. Pengantar

Selamat datang di bagian keempat Playlist codelab Dasar-Dasar Aplikasi dengan Google Spreadsheet.

Dengan menyelesaikan codelab ini, Anda dapat mempelajari cara memformat data spreadsheet di Apps Script, dan menulis fungsi untuk membuat spreadsheet terkelola yang terdiri dari data berformat yang diambil dari API publik.

Yang akan Anda pelajari

• Cara menerapkan berbagai operasi pemformatan Google Spreadsheet di Apps Script.
• Cara mengubah daftar objek JSON dan atributnya menjadi sheet data yang diatur dengan Apps Script.

Sebelum memulai

Ini adalah codelab keempat dalam playlist Fundamentals of Apps Script dengan Google Spreadsheet. Sebelum memulai codelab ini, pastikan untuk menyelesaikan codelab sebelumnya:

1. Makro dan Fungsi Kustom
2. Spreadsheet, Spreadsheet, dan Rentang
3. Bekerja dengan data

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

Sebelum melanjutkan, Anda memerlukan spreadsheet yang berisi beberapa data. Seperti sebelumnya, kami telah menyediakan lembar data yang dapat Anda salin untuk latihan ini. Lakukan langkah berikut:

1. Klik link ini untuk menyalin sheet data, lalu klik Buat salinan. Spreadsheet baru ditempatkan di folder Google Drive dan diberi nama "Salinan Pemformatan Data".
2. Klik judul spreadsheet dan ubah dari "Salinan Pemformatan Data" menjadi "Pemformatan Data". Sheet Anda akan terlihat seperti ini, dengan sejumlah informasi dasar tentang tiga film Star Wars pertama:

3. Pilih Ekstensi > Apps Script untuk membuka editor skrip.
4. Klik judul project Apps Script dan ubah dari "Project tanpa judul" menjadi "Format Data." Klik Ganti nama untuk menyimpan perubahan judul.

Dengan spreadsheet dan project ini, Anda siap untuk memulai codelab. Pindah ke bagian berikutnya untuk mulai mempelajari pemformatan dasar di Apps Script.

3. Membuat menu kustom

Anda dapat menerapkan beberapa metode pemformatan dasar di Apps Script ke Spreadsheet. Latihan berikut menunjukkan beberapa cara untuk memformat data. Untuk membantu mengontrol tindakan pemformatan Anda, mari buat menu khusus dengan item yang Anda butuhkan. Proses pembuatan menu kustom dijelaskan dalam codelab Bekerja dengan data, tetapi kami akan meringkasnya di sini lagi.

Implementasi

Mari buat menu khusus.

1. Di editor Apps Script, ganti kode dalam project skrip Anda dengan yang berikut ini:

/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the spreadsheet's user-interface object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
   .addItem('Format row header', 'formatRowHeader')
   .addItem('Format column header', 'formatColumnHeader')
   .addItem('Format dataset', 'formatDataset') 
  .addToUi();
}

2. Simpan project skrip Anda.
3. Di editor skrip, pilih onOpen dari daftar fungsi, lalu klik Jalankan. Tindakan ini menjalankan onOpen() untuk membuat ulang menu spreadsheet, jadi Anda tidak perlu memuat ulang spreadsheet.

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.

Baris berikutnya membuat menu (Quick formats), tambahkan item menu (Format row header, Format column header, dan Format dataset) ke menu, lalu tambahkan 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. Misalnya, memilih item menu Format row header akan menyebabkan Spreadsheet mencoba menjalankan fungsi formatRowHeader() (yang belum ada).

Hasil

Di spreadsheet, klik menu Quick formats untuk melihat item menu baru:

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

4. Memformat baris header

Set data di spreadsheet sering kali memiliki baris header untuk mengidentifikasi data di setiap kolom. Sebaiknya format baris header untuk memisahkannya secara visual dari sisa data di spreadsheet.

Di codelab pertama, Anda membuat makro untuk header dan menyesuaikan kodenya. Di sini, Anda akan memformat baris header dari awal menggunakan Apps Script. Baris header yang akan Anda buat akan menebalkan teks header, mewarnai latar belakang menjadi biru-hijau tua, mewarnai teks dengan putih, dan menambahkan beberapa batas solid.

Implementasi

Untuk menerapkan operasi pemformatan, Anda akan menggunakan metode layanan Spreadsheet yang sama dengan yang digunakan sebelumnya, tetapi kini Anda juga akan menggunakan beberapa metode pemformatan layanan. Lakukan langkah berikut:

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

/**
 * Formats top row of sheet using our header row style.
 */
function formatRowHeader() {
  // Get the current active sheet and the top row's range.
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
 
  // Apply each format to the top row: bold white text,
  // blue-green background, and a solid black border
  // around the cells.
  headerRange
    .setFontWeight('bold')
    .setFontColor('#ffffff')
    .setBackground('#007272')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

}

2. Simpan project skrip Anda.

Peninjauan kode

Seperti banyak tugas pemformatan, kode Apps Script dapat diterapkan dengan mudah. Dua baris pertama menggunakan metode yang Anda lihat sebelumnya untuk mendapatkan referensi ke sheet aktif saat ini (sheet) dan baris atas sheet (headerRange). Metode Sheet.getRange(row, column, numRows, numColumns) menentukan baris atas, termasuk hanya kolom yang berisi data di dalamnya. Metode Sheet.getLastColumn() menampilkan indeks kolom dari kolom terakhir yang berisi data dalam sheet. Pada contoh kami, kolomnya E (url).

Catatan: Di sini, kami berasumsi bahwa baris header yang ingin kami format selalu berada di baris 1, bahwa data kami dimulai di A1, dan diperluas di beberapa jumlah kolom yang berdekatan. Tidak masalah untuk latihan ini, tetapi secara umum Anda mungkin ingin menghindari asumsi ini dengan mengizinkan pengguna menentukan sel mana yang akan diformat (jika struktur spreadsheet mereka berbeda).

Bagian kode yang lain hanya memanggil berbagai metode Range untuk menerapkan pilihan pemformatan ke semua sel di headerRange. Agar kode mudah dibaca, kita menggunakan rantai metode untuk memanggil setiap metode pemformatan satu per satu:

• Range.setFontWeight(fontWeight) digunakan untuk menetapkan ketebalan font ke tebal.
• Range.setFontColor(color) digunakan untuk menetapkan warna font ke putih.
• Range.setBackground(color) digunakan untuk menetapkan warna latar belakang ke biru-hijau tua.
• setBorder(top, left, bottom, right, vertical, horizontal, color, style) menempatkan batas hitam solid di sekitar sel rentang.

Metode terakhir memiliki beberapa parameter, jadi mari kita tinjau apa yang dilakukan masing-masing parameter. Empat parameter pertama di sini (semua ditetapkan ke true) memberi tahu Apps Script bahwa batas harus ditambahkan di atas, di bawah, dan di sebelah kiri serta kanan rentang. Parameter kelima dan keenam (null dan null) mengarahkan Apps Script untuk menghindari perubahan garis batas dalam rentang yang dipilih. Parameter ketujuh (null) menunjukkan warna batas default ke hitam. Terakhir, parameter terakhir menentukan jenis gaya batas yang akan digunakan, yang diambil dari opsi yang disediakan oleh SpreadsheetApp.BorderStyle.

Hasil

Anda dapat melihat cara kerja fungsi format dengan melakukan hal berikut:

1. Jika belum, simpan project skrip Anda di editor Apps Script.
2. Klik item menu Format cepat > Format header baris.

Catatan: Jika menggunakan akun gmail.com, Anda mungkin mendapatkan dialog "Aplikasi ini tidak diverifikasi" saat pertama kali menggunakan skrip Anda. Google menggunakan ini untuk memperingatkan pengguna yang mungkin menggunakan kode dari penulis yang tidak dikenal atau tidak tepercaya. Jika Anda melihat dialog ini, tidak apa-apa untuk melanjutkan karena Anda adalah penulis skripnya. Ikuti petunjuk di layar untuk terus mengotorisasi skrip:

1. Pada dialog aplikasi yang belum diverifikasi, klik Lanjutan.
2. Klik Buka Manipulasi Data dan Menu Kustom (tidak aman).
3. Klik Izinkan.

Di seluruh codelab ini, Anda mungkin mendapatkan beberapa permintaan izin. Anda dapat membaca selengkapnya tentang proses ini di Otorisasi untuk Layanan Google.

Hasilnya akan terlihat seperti berikut:

Anda sekarang telah mengotomatiskan tugas pemformatan. Bagian berikutnya menerapkan teknik yang sama untuk membuat gaya format lain dengan header kolom.

5. Memformat header kolom

Jika Anda dapat membuat header baris yang dipersonalisasi, Anda juga dapat membuat header kolom. Header kolom meningkatkan keterbacaan untuk set data tertentu. Misalnya, kolom titles di spreadsheet ini dapat disempurnakan dengan pilihan format berikut:

• Menebalkan teks
• Miringkan teks
• Menambahkan batas sel
• Menyisipkan hyperlink, menggunakan konten kolom url. Setelah menambahkan hyperlink, Anda dapat menghapus kolom url untuk membantu membersihkan sheet.

Berikutnya, Anda akan menerapkan fungsi formatColumnHeader() untuk menerapkan perubahan ini ke kolom pertama dalam sheet. Untuk membantu membuat kode sedikit lebih mudah dibaca, Anda juga akan menerapkan dua fungsi bantuan.

Implementasi

Seperti sebelumnya, Anda perlu menambahkan fungsi untuk mengotomatiskan pemformatan header kolom. Lakukan langkah berikut:

1. Di editor Apps Script, tambahkan fungsi formatColumnHeader() berikut di akhir project skrip Anda:

/**
 * Formats the column header of the active sheet.
 */ 
function formatColumnHeader() {  
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Get total number of rows in data range, not including
  // the header row.
  var numRows = sheet.getDataRange().getLastRow() - 1;
  
  // Get the range of the column header.
  var columnHeaderRange = sheet.getRange(2, 1, numRows, 1);
  
  // Apply text formatting and add borders.
  columnHeaderRange
    .setFontWeight('bold')
    .setFontStyle('italic')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
 
  // Call helper method to hyperlink the first column contents
  // to the url column contents.
  hyperlinkColumnHeaders_(columnHeaderRange, numRows); 
}

2. Tambahkan fungsi bantuan berikut ke akhir project skrip Anda, setelah fungsi formatColumnHeader():

/**
 * Helper function that hyperlinks the column header with the
 * 'url' column contents. The function then removes the column.
 *
 * @param {object} headerRange The range of the column header
 *   to update.
 * @param {number} numRows The size of the column header.
 */
function hyperlinkColumnHeaders_(headerRange, numRows) {
  // Get header and url column indices.
  var headerColIndex = 1; 
  var urlColIndex = columnIndexOf_('url');  
  
  // Exit if the url column is missing.
  if(urlColIndex == -1)
    return; 
  
  // Get header and url cell values.
  var urlRange =
    headerRange.offset(0, urlColIndex - headerColIndex);
  var headerValues = headerRange.getValues();
  var urlValues = urlRange.getValues();
  
  // Updates header values to the hyperlinked header values.
  for(var row = 0; row < numRows; row++){
    headerValues[row][0] = '=HYPERLINK("' + urlValues[row]
      + '","' + headerValues[row] + '")';
  }
  headerRange.setValues(headerValues);
  
  // Delete the url column to clean up the sheet.
  SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex);
}

/**
 * Helper function that goes through the headers of all columns
 * and returns the index of the column with the specified name
 * in row 1. If a column with that name does not exist,
 * this function returns -1. If multiple columns have the same
 * name in row 1, the index of the first one discovered is
 * returned.
 * 
 * @param {string} colName The name to find in the column
 *   headers. 
 * @return The index of that column in the active sheet,
 *   or -1 if the name isn't found.
 */ 
function columnIndexOf_(colName) {
  // Get the current column names.
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnHeaders =
    sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var columnNames = columnHeaders.getValues();
  
  // Loops through every column and returns the column index
  // if the row 1 value of that column matches colName.
  for(var col = 1; col <= columnNames[0].length; col++)
  {
    if(columnNames[0][col-1] === colName)
      return col; 
  }

  // Returns -1 if a column named colName does not exist. 
  return -1; 
}

3. Simpan project skrip Anda.

Peninjauan kode

Mari kita tinjau kode di masing-masing dari tiga fungsi ini secara terpisah:

formatColumnHeader()

Seperti yang mungkin Anda harapkan, beberapa baris pertama dari fungsi ini menetapkan variabel yang mereferensikan sheet dan rentang yang kami minati:

• Sheet aktif disimpan di sheet.
• Jumlah baris di header kolom dihitung dan disimpan di numRows. Di sini kode mengurangkan satu sehingga jumlah baris tidak menyertakan header kolom: title.
• Rentang yang mencakup header kolom disimpan di columnHeaderRange.

Kemudian, kode menerapkan batas dan menebalkan rentang header kolom, sama seperti di formatRowHeader(). Di sini, Range.setFontStyle(fontStyle) juga digunakan untuk membuat teks dicetak miring.

Menambahkan hyperlink ke kolom header lebih rumit, sehingga formatColumnHeader() memanggil hyperlinkColumnHeaders_(headerRange, numRows) untuk mengerjakan tugas. Hal ini membantu menjaga kode tetap rapi dan dapat dibaca.

hyperlinkColumnHeaders_(headerRange, numRows)

Fungsi bantuan ini terlebih dahulu mengidentifikasi indeks kolom header (diasumsikan sebagai indeks 1) dan kolom url. Metode ini memanggil columnIndexOf_('url') untuk mendapatkan indeks kolom URL. Jika kolom url tidak ditemukan, metode akan keluar tanpa mengubah data apa pun.

Fungsi ini mendapatkan rentang baru (urlRange) yang mencakup URL yang sesuai dengan baris kolom header. Hal ini dilakukan dengan metode Range.offset(rowOffset, columnOffset), yang menjamin kedua rentang tersebut akan memiliki ukuran yang sama. Nilai di kolom headerColumn dan url kemudian diambil (headerValues dan urlValues).

Fungsi ini kemudian melakukan loop pada setiap nilai sel header kolom dan menggantinya dengan formula Spreadsheet =HYPERLINK() yang dibuat dengan konten kolom dan url kolom. Nilai header yang diubah kemudian disisipkan ke sheet menggunakan Range.setValues(values).

Terakhir, untuk membantu menjaga sheet tetap bersih dan menghilangkan informasi yang berlebihan, Sheet.deleteColumn(columnPosition) akan diminta untuk menghapus kolom url.

columnIndexOf_(colName)

Fungsi bantuan ini hanyalah fungsi utilitas sederhana yang menelusuri baris pertama sheet untuk mencari nama tertentu. Tiga baris pertama menggunakan metode yang telah Anda lihat untuk mendapatkan daftar nama header kolom dari baris 1 spreadsheet. Nama-nama ini disimpan dalam variabel fieldNames.

Fungsi tersebut kemudian meninjau setiap nama secara berurutan. Jika menemukan kolom yang cocok dengan nama yang ditelusuri, kolom tersebut akan berhenti dan menampilkan indeks kolom. Jika mencapai akhir daftar nama tanpa menemukan nama, ia menampilkan -1 untuk menandakan bahwa nama tidak ditemukan.

Hasil

Anda dapat melihat cara kerja fungsi format dengan melakukan hal berikut:

1. Jika belum, simpan project skrip Anda di editor Apps Script.
2. Klik item menu Format cepat > Header kolom format.

Hasilnya akan terlihat seperti berikut:

Anda sekarang telah mengotomatiskan tugas pemformatan lainnya. Dengan format header kolom dan baris, bagian berikutnya menunjukkan cara memformat data.

6. Memformat set data

Setelah Anda memiliki header, mari kita buat fungsi untuk memformat sisa data dalam sheet Anda. Kami akan menggunakan opsi pemformatan berikut:

• Warna latar belakang baris alternatif (dikenal sebagai banding)
• Mengubah format tanggal
• Menerapkan batas
• Mengubah ukuran semua kolom dan baris secara otomatis

Sekarang Anda akan membuat fungsi formatDataset() dan metode bantuan tambahan untuk menerapkan format ini ke data sheet Anda.

Implementasi

Seperti sebelumnya, tambahkan fungsi untuk mengotomatiskan pemformatan data. Lakukan langkah berikut:

1. Di editor Apps Script, tambahkan fungsi formatDataset() berikut di akhir project skrip Anda:

/**
 * Formats the sheet data, excluding the header row and column.
 * Applies the border and banding, formats the 'release_date'
 * column, and autosizes the columns and rows.
 */
function formatDataset() {
  // Get the active sheet and data range.
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var fullDataRange = sheet.getDataRange();

  // Apply row banding to the data, excluding the header
  // row and column. Only apply the banding if the range
  // doesn't already have banding set.
  var noHeadersRange = fullDataRange.offset(
    1, 1,
    fullDataRange.getNumRows() - 1,
    fullDataRange.getNumColumns() - 1);

  if (! noHeadersRange.getBandings()[0]) {
    // The range doesn't already have banding, so it's
    // safe to apply it.
    noHeadersRange.applyRowBanding(
      SpreadsheetApp.BandingTheme.LIGHT_GREY,
      false, false);
  }

  // Call a helper function to apply date formatting
  // to the column labeled 'release_date'.
  formatDates_( columnIndexOf_('release_date') );
  
  // Set a border around all the data, and resize the
  // columns and rows to fit.
  fullDataRange.setBorder(
    true, true, true, true, null, null,
    null,
    SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  sheet.autoResizeColumns(1, fullDataRange.getNumColumns());
  sheet.autoResizeRows(1, fullDataRange.getNumRows());
}

2. Tambahkan fungsi bantuan berikut di akhir project skrip Anda, setelah fungsi formatDataset():

/** 
 * Helper method that applies a
 * "Month Day, Year (Day of Week)" date format to the
 * indicated column in the active sheet. 
 *
 * @param {number} colIndex The index of the column
 *   to format.
 */ 
function formatDates_(colIndex) {
  // Exit if the given column index is -1, indicating
  // the column to format isn't present in the sheet.
  if (colIndex < 0)
    return; 

  // Set the date format for the date column, excluding
  // the header row.
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)
    .setNumberFormat("mmmm dd, yyyy (dddd)");
}

3. Simpan project skrip Anda.

Peninjauan kode

Mari kita tinjau kode di setiap fungsi berikut secara terpisah:

formatDataset()

Fungsi ini mengikuti pola yang serupa dengan fungsi format sebelumnya yang telah Anda terapkan. Pertama, mendapatkan variabel untuk menyimpan referensi ke sheet aktif (sheet) dan rentang data (fullDataRange).

Kedua, metode Range.offset(rowOffset, columnOffset, numRows, numColumns) akan digunakan untuk membuat rentang (noHeadersRange) yang mencakup semua data dalam sheet, tidak termasuk header kolom dan baris. Kode kemudian memverifikasi apakah rentang baru ini memiliki banding yang ada (menggunakan Range.getBandings()). Ini diperlukan karena Apps Script menampilkan error jika Anda mencoba menerapkan banding baru di tempat yang sudah ada. Jika banding tidak ada, fungsi ini akan menambahkan banding berwarna abu-abu muda menggunakan Range.applyRowBanding(bandingTheme, showHeader, showFooter). Jika tidak, fungsi akan berubah.

Langkah selanjutnya memanggil fungsi bantuan formatDates_(colIndex) untuk memformat tanggal di kolom berlabel 'release_date' (dijelaskan di bawah). Kolom ditetapkan menggunakan fungsi bantuan columnIndexOf_(colName) yang Anda implementasikan sebelumnya.

Terakhir, pemformatan selesai dengan menambahkan batas lain (seperti sebelumnya), dan secara otomatis mengubah ukuran setiap kolom dan baris agar sesuai dengan data yang ada di dalamnya menggunakan metode Sheet.autoResizeColumns(columnPosition) dan Sheet.autoResizeColumns(columnPosition).

formatDates_(colIndex)

Fungsi bantuan ini menerapkan format tanggal tertentu ke kolom menggunakan indeks kolom yang disediakan. Secara khusus, opsi ini memformat nilai tanggal sebagai "Bulan Hari, Tahun (Hari dalam Seminggu)".

Catatan: Ada banyak format tanggal dan angka yang dapat Anda gunakan dalam skrip dan spreadsheet. Untuk informasi selengkapnya, tinjau panduan Format Tanggal dan Angka.

Pertama, fungsi memverifikasi bahwa indeks kolom yang diberikan valid (yaitu, 0 atau lebih besar). Jika tidak, aplikasi akan kembali tanpa melakukan apa pun. Pemeriksaan ini mencegah error yang mungkin terjadi jika, misalnya, sheet tidak memiliki kolom ‘release_date'

Setelah indeks kolom divalidasi, fungsi tersebut akan mendapatkan rentang yang mencakup kolom tersebut (tidak termasuk baris header-nya) dan menggunakan Range.setNumberFormat(numberFormat) untuk menerapkan format.

Hasil

Anda dapat melihat cara kerja fungsi format dengan melakukan hal berikut:

1. Jika belum, simpan project skrip Anda di editor Apps Script.
2. Klik item menu Format cepat > Format set data.

Hasilnya akan terlihat seperti berikut:

Anda telah mengotomatiskan tugas pemformatan lainnya. Setelah perintah pemformatan ini tersedia, mari tambahkan lebih banyak data untuk diterapkan.

7. Mengambil dan memformat data API

Sejauh codelab ini, Anda telah melihat cara menggunakan Apps Script sebagai cara alternatif untuk memformat spreadsheet. Selanjutnya Anda akan menulis kode yang mengambil data dari API publik, memasukkannya ke dalam spreadsheet Anda, dan memformatnya agar dapat dibaca.

Pada codelab terakhir, Anda telah mempelajari cara mengambil data dari API. Anda akan menggunakan teknik yang sama di sini. Dalam latihan ini, kami akan menggunakan Star Wars API (SWAPI) publik untuk mengisi spreadsheet Anda. Secara khusus, Anda akan menggunakan API untuk mendapatkan informasi tentang karakter utama yang muncul dalam tiga film Star Wars asli.

Kode Anda akan memanggil API untuk mendapatkan data JSON dalam jumlah besar, menguraikan respons, menempatkan data dalam sheet baru, kemudian memformat sheet.

Implementasi

Di bagian ini, Anda akan menambahkan beberapa item menu tambahan. Setiap item menu akan memanggil skrip wrapper yang meneruskan variabel khusus item ke fungsi utama (createResourceSheet_()). Anda akan menerapkan fungsi ini, dan tiga fungsi bantuan tambahan. Seperti sebelumnya, fungsi bantuan membantu mengisolasi bagian kompartemen secara logis dari tugas dan membantu menjaga kode tetap dapat dibaca.

Lakukan tindakan berikut:

1. Di editor Apps Script, update fungsi onOpen() dalam project skrip Anda agar cocok dengan berikut ini:

/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the Ui object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
    .addItem('Format row header', 'formatRowHeader')
    .addItem('Format column header', 'formatColumnHeader')
    .addItem('Format dataset', 'formatDataset')
    .addSeparator()
    .addSubMenu(ui.createMenu('Create character sheet')
                .addItem('Episode IV', 'createPeopleSheetIV')
                .addItem('Episode V', 'createPeopleSheetV')
                .addItem('Episode VI', 'createPeopleSheetVI')
                )
    .addToUi();
}

2. Simpan project skrip Anda.
3. Di editor skrip, pilih onOpen dari daftar fungsi, lalu klik Jalankan. Ini menjalankan onOpen() untuk membuat ulang menu spreadsheet dengan opsi baru yang Anda tambahkan.
4. Untuk membuat file Apps Script, di samping File klik Tambahkan file > Script.
5. Beri nama skrip baru "API" lalu tekan Enter. (Apps Script secara otomatis menambahkan ekstensi .gs ke nama file skrip.)
6. Ganti kode di file API.gs baru dengan kode berikut:

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode IV.
 */
function createPeopleSheetIV() {
  createResourceSheet_('characters', 1, "IV");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode V.
 */
function createPeopleSheetV() {
  createResourceSheet_('characters', 2, "V");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode VI.
 */
function createPeopleSheetVI() {
  createResourceSheet_('characters', 3, "VI");
}

/** 
 * Creates a formatted sheet filled with user-specified
 * information from the Star Wars API. If the sheet with
 * this data exists, the sheet is overwritten with the API
 * information.
 *
 * @param {string} resourceType The type of resource.
 * @param {number} idNumber The identification number of the film.
 * @param {number} episodeNumber The Star Wars film episode number.
 *   This is only used in the sheet name.
 */
function createResourceSheet_(
    resourceType, idNumber, episodeNumber) { 
  
  // Fetch the basic film data from the API. 
  var filmData = fetchApiResourceObject_(
      "https://swapi.dev/api/films/" + idNumber);

  // Extract the API URLs for each resource so the code can
  // call the API to get more data about each individually.
  var resourceUrls = filmData[resourceType];
  
  // Fetch each resource from the API individually and push
  // them into a new object list.
  var resourceDataList = []; 
  for(var i = 0; i < resourceUrls.length; i++){
    resourceDataList.push(
      fetchApiResourceObject_(resourceUrls[i])
    ); 
  } 
  
  // Get the keys used to reference each part of data within
  // the resources. The keys are assumed to be identical for
  // each object since they're all the same resource type.
  var resourceObjectKeys = Object.keys(resourceDataList[0]);
  
  // Create the sheet with the appropriate name. It
  // automatically becomes the active sheet when it's created.
  var resourceSheet = createNewSheet_(
      "Episode " + episodeNumber + " " + resourceType);
  
  // Add the API data to the new sheet, using each object
  // key as a column header. 
  fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);
  
  // Format the new sheet using the same styles the
  // 'Quick Formats' menu items apply. These methods all
  // act on the active sheet, which is the one just created.
  formatRowHeader();
  formatColumnHeader();   
  formatDataset();

}

6. Tambahkan fungsi bantuan berikut ke akhir file project skrip API.gs:

/** 
 * Helper function that retrieves a JSON object containing a
 * response from a public API.
 *
 * @param {string} url The URL of the API object being fetched.
 * @return {object} resourceObject The JSON object fetched
 *   from the URL request to the API.
 */
function fetchApiResourceObject_(url) {
  // Make request to API and get response.
  var response =
    UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  
  // Parse and return the response as a JSON object.
  var json = response.getContentText();
  var responseObject = JSON.parse(json); 
  return responseObject; 
}

/** 
 * Helper function that creates a sheet or returns an existing
 * sheet with the same name.
 *
 * @param {string} name The name of the sheet.
 * @return {object} The created or existing sheet
 *   of the same name. This sheet becomes active.
 */ 
function createNewSheet_(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Returns an existing sheet if it has the specified
  // name. Activates the sheet before returning.
  var sheet = ss.getSheetByName(name);
  if (sheet) {
    return sheet.activate();
  }
  
  // Otherwise it makes a sheet, set its name, and returns it.
  // New sheets created this way automatically become the active
  // sheet.
  sheet = ss.insertSheet(name); 
  return sheet; 
}

/** 
 * Helper function that adds API data to the sheet.
 * Each object key is used as a column header in the new sheet.
 *
 * @param {object} resourceSheet The sheet object being modified.
 * @param {object} objectKeys The list of keys for the resources.
 * @param {object} resourceDataList The list of API
 *   resource objects containing data to add to the sheet.
 */
function fillSheetWithData_(
    resourceSheet, objectKeys, resourceDataList) {
  // Set the dimensions of the data range being added to the sheet.
  var numRows = resourceDataList.length;
  var numColumns = objectKeys.length;
  
  // Get the resource range and associated values array. Add an
  // extra row for the column headers.
  var resourceRange =
    resourceSheet.getRange(1, 1, numRows + 1, numColumns);
  var resourceValues = resourceRange.getValues(); 
  
  // Loop over each key value and resource, extracting data to
  // place in the 2D resourceValues array.
  for (var column = 0; column < numColumns; column++) {

    // Set the column header.
    var columnHeader = objectKeys[column];
    resourceValues[0][column] = columnHeader;
    
    // Read and set each row in this column.
    for (var row = 1; row < numRows + 1; row++) {
      var resource = resourceDataList[row - 1];
      var value = resource[columnHeader];
      resourceValues[row][column] = value;
    }
  }
  
  // Remove any existing data in the sheet and set the new values.
  resourceSheet.clear()
  resourceRange.setValues(resourceValues);
}

7. Simpan project skrip Anda.

Peninjauan kode

Anda baru saja menambahkan banyak kode. Mari kita bahas setiap fungsi satu per satu untuk memahami cara kerjanya:

onOpen()

Di sini Anda telah menambahkan beberapa item menu ke menu Quick formats. Anda telah menyetel garis pemisah, lalu menggunakan metode Menu.addSubMenu(menu) untuk membuat struktur menu bertingkat dengan tiga item baru. Item baru ditambahkan dengan metode Menu.addItem(caption, functionName).

Fungsi wrapper

Item menu yang ditambahkan semuanya melakukan hal yang serupa: item tersebut mencoba membuat sheet dengan data yang diambil dari SWAPI. Satu-satunya perbedaan adalah masing-masing berfokus pada film yang berbeda.

Akan lebih mudah untuk menulis fungsi tunggal untuk membuat sheet, dan meminta fungsi menerima parameter untuk menentukan film apa yang akan digunakan. Namun, metode Menu.addItem(caption, functionName) tidak mengizinkan Anda meneruskan parameter ke metode tersebut saat dipanggil oleh menu. Jadi, bagaimana Anda menghindari menulis kode yang sama tiga kali?

Jawabannya adalah fungsi wrapper. Ini adalah fungsi ringan yang dapat Anda panggil yang segera memanggil fungsi lain dengan parameter tertentu yang ditetapkan.

Di sini, kode menggunakan tiga fungsi wrapper: createPeopleSheetIV(), createPeopleSheetV(), dan createPeopleSheetVI(). Item menu ditautkan ke fungsi ini. Saat item menu diklik, fungsi wrapper akan dieksekusi dan segera memanggil fungsi builder sheet utama createResourceSheet_(resourceType, idNumber, episodeNumber), dengan meneruskan parameter yang sesuai untuk item menu. Dalam hal ini, ini berarti meminta fungsi builder sheet untuk membuat sheet yang berisi data karakter utama dari salah satu film Star Wars.

Catatan: Jika diinginkan, Anda dapat membuat lebih banyak fungsi wrapper untuk mengisi berbagai jenis sheet dengan data. Anda hanya perlu mengetahui jenis resource yang akan diminta dan ID film yang akan digunakan. Contoh jenis resource mencakup ‘planets' dan ‘starships', serta API menerima angka 1–7 sebagai ID film.

createResourceSheet_(resourceType, idNumber, episodeNumber)

Ini adalah fungsi pembuat sheet utama untuk latihan ini. Dengan bantuan beberapa fungsi bantuan, fitur ini mendapatkan data API, menguraikannya, membuat sheet, menulis data API ke sheet, lalu memformat sheet menggunakan fungsi yang Anda buat di bagian sebelumnya. Mari kita tinjau detailnya:

Pertama, fungsi menggunakan fetchApiResourceObject_(url) untuk membuat permintaan API untuk mengambil informasi film dasar. Respons API mencakup sekumpulan URL yang dapat digunakan kode untuk mendapatkan detail selengkapnya tentang orang tertentu (dikenal di sini sebagai referensi) dari film. Kode ini mengumpulkan semuanya dalam array resourceUrls.

Berikutnya, kode menggunakan fetchApiResourceObject_(url) berulang kali untuk memanggil API bagi setiap URL resource di resourceUrls. Hasilnya disimpan dalam array resourceDataList. Setiap elemen array ini adalah objek yang menjelaskan karakter yang berbeda dari film.

Objek data resource memiliki beberapa kunci umum yang dipetakan ke informasi tentang karakter tersebut. Misalnya, kunci ‘name' memetakan ke nama karakter dalam film. Kami berasumsi bahwa kunci untuk setiap objek data resource semuanya identik, karena objek tersebut dimaksudkan untuk menggunakan struktur objek umum. Daftar kunci diperlukan nanti, sehingga kode menyimpan daftar kunci dalam resourceObjectKeys menggunakan metode Object.keys() JavaScript.

Selanjutnya, fungsi builder memanggil fungsi bantuan createNewSheet_(name) untuk membuat sheet tempat data baru akan ditempatkan. Memanggil fungsi bantuan ini juga akan mengaktifkan sheet baru.

Setelah sheet dibuat, fungsi bantuan fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) dipanggil untuk menambahkan semua data API ke sheet.

Terakhir, semua fungsi pemformatan yang Anda buat sebelumnya akan dipanggil untuk menerapkan aturan pemformatan yang sama ke data baru. Karena sheet baru adalah yang aktif, kode dapat menggunakan kembali fungsi ini tanpa modifikasi.

fetchApiResourceObject_(url)

Fungsi bantuan ini mirip dengan fungsi bantuan fetchBookData_(ISBN) yang digunakan di codelab sebelumnya Bekerja dengan data. Diperlukan URL yang ditentukan dan menggunakan metode UrlFetchApp.fetch(url, params) untuk mendapatkan respons. Respons tersebut kemudian diuraikan menjadi objek JSON menggunakan metode HTTPResponse.getContextText() dan JSON.parse(json) JavaScript. Objek JSON yang dihasilkan kemudian ditampilkan.

Catatan: Jika Anda banyak bekerja dengan API, fungsi ini dapat berguna ketika Anda perlu mengambil informasi API. Secara umum, URL ini berfungsi dengan hampir semua URL API, jadi pertimbangkan untuk menyimpannya untuk penggunaan di masa mendatang.

createNewSheet_(name)

Fungsi bantuan ini cukup sederhana. Pertama-tama, verifikasi apakah sheet dengan nama yang diberikan ada di spreadsheet. Jika ya, fungsi akan mengaktifkan sheet dan menampilkannya.

Jika sheet tidak ada, fungsi akan membuatnya dengan Spreadsheet.insertSheet(sheetName), mengaktifkannya, dan menampilkan sheet baru.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

Fungsi bantuan ini bertanggung jawab untuk mengisi sheet baru dengan data API. Fungsi ini mengambil parameter baru sheet, daftar kunci objek, dan daftar objek resource API sebagai parameter. Setiap kunci objek mewakili kolom dalam sheet baru, dan setiap objek resource mewakili baris.

Pertama, fungsi menghitung jumlah baris dan kolom yang diperlukan untuk menyajikan data API baru. Ini adalah ukuran masing-masing daftar resource dan kunci. Fungsi tersebut kemudian menentukan rentang output (resourceRange) tempat data akan ditempatkan, dan menambahkan baris tambahan untuk menampung header kolom. Variabel resourceValues menyimpan array nilai 2D yang diekstrak dari resourceRange.

Fungsi tersebut kemudian melakukan loop di atas setiap kunci objek dalam daftar objectKeys. Kunci ini ditetapkan sebagai header kolom, lalu loop kedua melewati setiap objek resource. Untuk setiap pasangan (baris, kolom), informasi API yang sesuai akan disalin ke elemen resourceValues[row][column].

Catatan: Ingat bahwa baris dan kolom di Spreadsheet diindeks 1, sedangkan array JavaScript diindeks 0. Karena perbedaan ini, kami sering harus menambahkan atau mengurangi 1 dari indeks saat memprogram di Apps Script.

Setelah resourceValues diisi, sheet tujuan dihapus menggunakan Sheet.clear() jika berisi data dari klik item menu sebelumnya. Terakhir, nilai baru akan ditulis ke sheet.

Hasil

Anda dapat melihat hasil tugas Anda dengan melakukan hal berikut:

1. Jika belum, simpan project skrip Anda di editor Apps Script.
2. Klik item menu Format cepat > Buat sheet karakter > Episode IV.

Hasilnya akan terlihat seperti berikut:

Anda kini telah menulis kode untuk mengimpor data ke Spreadsheet dan memformatnya secara otomatis.

8 Kesimpulan

Selamat, Anda telah menyelesaikan codelab ini. Anda telah melihat beberapa opsi pemformatan Spreadsheet yang dapat Anda sertakan dalam project Apps Script, dan membuat aplikasi mengesankan yang mengimpor dan memformat set data API besar.

Apakah codelab ini bermanfaat bagi Anda?

Ya Tidak

Yang telah Anda pelajari

• Cara menerapkan berbagai operasi pemformatan Spreadsheet dengan Apps Script.
• Cara membuat submenu dengan fungsi onOpen().
• Cara memformat daftar objek JSON yang diambil ke sheet data baru dengan Apps Script.

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 klien-server. 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.
• Fungsi wrapper: Fungsi utilitas sederhana yang digunakan untuk memanggil fungsi yang lebih kompleks saat meneruskan kumpulan parameter tertentu.

Apa selanjutnya

Codelab berikutnya dalam playlist ini menunjukkan cara menggunakan Apps Script untuk memvisualisasikan data dalam diagram dan mengekspor diagram ke presentasi Google Slide.

Temukan codelab berikutnya di Diagram dan menyajikan data di Slide.