Ubah big data Anda menjadi insight menggunakan Google Spreadsheet dan Slide

1. Dari analisis {i>big data<i} hingga presentasi {i>slide<i}

Ada banyak alat bagi data scientist untuk melakukan analisis {i>big data<i}, tetapi pada akhirnya analis masih harus membenarkan hasil kepada manajemen. Banyak angka di atas kertas atau dalam {i>database<i} sulit untuk disajikan kepada para pemangku kepentingan utama. Codelab Google Apps Script tingkat menengah ini menggunakan 2 platform developer Google (Google Workspace dan Konsol Google Cloud) untuk membantu Anda mengotomatiskan tahap akhir tersebut.

Alat developer Google Cloud memungkinkan Anda melakukan analisis data mendalam. Anda kemudian dapat mengambil hasilnya, memasukkannya ke dalam {i>spreadsheet<i}, dan membuat presentasi {i>slide<i} dengan data tersebut. Hal ini menawarkan cara yang lebih cocok untuk mengirimkan data ke manajemen. Codelab ini mencakup API BigQuery Konsol Cloud (sebagai layanan lanjutan Apps Script) dan layanan Apps Script bawaan untuk Google Spreadsheet dan Google Slide.

Motivasi

Aplikasi contoh dalam codelab ini terinspirasi oleh contoh kode lainnya berikut:

• Aplikasi contoh Google Apps Script BigQuery Service yang bersifat open source di GitHub.
• Contoh aplikasi yang ditampilkan dalam video developer Membuat slide dari data spreadsheet dan dipublikasikan dalam postingan blog ini.
• Aplikasi contoh yang ditampilkan di codelab Google Slides API.

Meskipun aplikasi contoh codelab Slides API juga menampilkan BigQuery dan Slide, aplikasi contoh ini berbeda dengan aplikasi contoh codelab ini dalam beberapa hal:

• Aplikasi Node.js mereka vs. aplikasi Apps Script.
• Mereka menggunakan REST API saat kami menggunakan layanan Apps Script.
• Mereka menggunakan Google Drive tetapi tidak Google Spreadsheet sedangkan aplikasi ini menggunakan Spreadsheet, tetapi tidak Drive.

Untuk codelab ini, kami ingin menyatukan beberapa teknologi ke dalam satu aplikasi sekaligus menampilkan fitur dan API dari seluruh Google Cloud dengan cara yang menyerupai kasus penggunaan di kehidupan nyata. Tujuannya adalah menginspirasi Anda untuk menggunakan imajinasi Anda serta mempertimbangkan untuk menggunakan Konsol Cloud dan Google Workspace guna memecahkan masalah yang menantang bagi organisasi dan pelanggan Anda.

Yang akan Anda pelajari

• Cara menggunakan Google Apps Script dengan beberapa layanan Google
• Cara menggunakan Google BigQuery untuk menganalisis big data
• Cara membuat Spreadsheet Google dan menyisipkan data ke dalamnya
• Cara membuat diagram di Spreadsheet
• Cara mentransfer data dan diagram dari Spreadsheet ke presentasi Google Slide

Yang Anda butuhkan

• Browser web dengan akses ke internet
• Akun Google (akun Google Workspace mungkin memerlukan persetujuan administrator)
• Pemahaman dasar tentang Google Spreadsheet
• Kemampuan untuk membaca Notasi A1 Spreadsheet
• Keterampilan JavaScript dasar
• Pengetahuan tentang pengembangan Apps Script membantu, tetapi tidak wajib

2. Survei

Bagaimana Anda akan menggunakan codelab/tutorial ini?

Membacanya untuk tujuan informasi, mungkin akan menyampaikannya kepada kolega teknis Lakukan sebanyak mungkin dan cobalah latihan sebanyak mungkin Jangan khawatir, saya akan menyelesaikan seluruh codelab

Bagaimana penilaian Anda terhadap pengalaman menggunakan alat developer Google Workspace & API?

Pemula Menengah Mahir

Bagaimana penilaian Anda terhadap pengalaman Anda dengan Apps Script?

Pemula Menengah Mahir

Bagaimana penilaian Anda terhadap pengalaman Anda menggunakan alat developer Konsol Cloud & API?

Pemula Menengah Mahir

3. Ringkasan

Setelah Anda mengetahui apa yang dimaksud dengan codelab ini, berikut yang akan Anda lakukan:

1. Ambil contoh Apps Script-BigQuery yang ada, lalu buat contoh tersebut berfungsi.
2. Dari sampel tersebut, pelajari cara mengirim kueri ke BigQuery dan mendapatkan hasilnya.
3. Buat Spreadsheet Google dan masukkan hasil dari BigQuery ke dalamnya.
4. Ubah kode untuk sedikit mengubah data yang ditampilkan dan dimasukkan ke dalam Spreadsheet.
5. Gunakan layanan Spreadsheet di Apps Script untuk membuat diagram untuk data BigQuery.
6. Gunakan layanan Slide untuk membuat presentasi Google Slide.
7. Menambahkan judul dan subjudul ke {i>slide<i} judul default.
8. Buat slide dengan tabel data dan ekspor sel data Spreadsheet ke dalamnya.
9. Buat {i>slide<i} lain dan sisipkan bagan {i>spreadsheet<i} ke dalamnya.

Mari kita mulai dengan beberapa informasi latar belakang tentang Apps Script, BigQuery, Spreadsheet, dan Slide.

Google Apps Script dan BigQuery

Google Apps Script adalah platform pengembangan Google Workspace yang beroperasi di level lebih tinggi daripada Google REST API. Ini adalah lingkungan pengembangan dan hosting aplikasi serverless yang dapat diakses oleh semua tingkat keterampilan developer. Pada dasarnya, Apps Script adalah runtime JavaScript serverless untuk otomatisasi, ekstensi, dan integrasi Google Workspace.

Cloud SQL menggunakan JavaScript sisi server, mirip dengan Node.js, tetapi berfokus pada integrasi erat dengan Google Workspace dan layanan Google lainnya, bukan hosting aplikasi berbasis peristiwa yang cepat dan asinkron. Alat ini juga menampilkan lingkungan pengembangan yang mungkin berbeda dari yang biasa Anda gunakan. Dengan Apps Script, Anda:

• Mengembangkan skrip menggunakan editor kode berbasis browser, tetapi juga dapat mengembangkan skrip secara lokal saat menggunakan clasp, alat deployment command line untuk Apps Script.
• Tulis kode dalam versi JavaScript khusus yang disesuaikan untuk mengakses Google Workspace dan layanan Google atau eksternal lainnya (menggunakan layanan URL Fetch atau JDBC Apps Script).
• Anda dapat menghindari penulisan kode otorisasi karena Apps Script akan menanganinya untuk Anda.
• Aplikasi Anda tidak harus dihosting—aplikasi tersebut ada dan berjalan di server Google di cloud.

CATATAN: Hal ini berada di luar cakupan codelab ini untuk mengajarkan Apps Script kepada Anda. Ada banyak referensi {i>online<i} untuk membantu Anda mengenai hal ini. Dokumentasi resmi juga menampilkan ringkasan dengan panduan memulai, tutorial, dan video. Selain itu, 2 codelab pengantar berikut harus diselesaikan sebelum memulai codelab ini: Dasar-dasar Apps Script dengan Google Spreadsheet dan Praktik langsung dengan Google Apps Script.

Apps Script berinteraksi dengan teknologi Google lain dalam 2 cara berbeda:

• Sebagai layanan bawaan
• Sebagai layanan lanjutan

Layanan bawaan memiliki metode tingkat tinggi untuk berinteraksi dengan data pengguna, sistem Google lainnya, dan sistem eksternal. Layanan lanjutan pada dasarnya adalah wrapper tipis di sekitar Google Workspace API atau Google REST API. Layanan lanjutan menyediakan cakupan penuh REST API dan sering kali dapat melakukan lebih dari sekadar layanan bawaan, tetapi layanan tersebut memerlukan lebih banyak kerumitan kode (sekaligus tetap lebih mudah digunakan daripada REST API lengkap). Layanan lanjutan juga harus diaktifkan untuk project skrip sebelum menggunakannya.

Jika memungkinkan, developer harus menggunakan layanan bawaan karena lebih mudah digunakan dan melakukan lebih dari sekadar layanan lanjutan. Namun, beberapa Google API tidak memiliki layanan bawaan, jadi layanan lanjutan mungkin menjadi satu-satunya pilihan. Misalnya, Google BigQuery tidak memiliki layanan bawaan, tetapi Layanan BigQuery memang ada. Layanan BigQuery adalah layanan Konsol Cloud yang memungkinkan Anda menggunakan Google BigQuery API untuk menjalankan kueri pada korpus data besar (misalnya, beberapa terabyte), tetapi masih dapat memberikan hasilnya dalam hitungan detik.

Akses Spreadsheet & Slide dari Apps Script

Tidak seperti BigQuery, Spreadsheet dan Slide memiliki layanan bawaan. Library ini juga memiliki layanan lanjutan untuk mengakses fitur yang hanya ditemukan di API. Lihat dokumen untuk layanan Spreadsheet dan Slide bawaan sebelum langsung masuk ke kode. Perhatikan bahwa, ada juga dokumen untuk layanan lanjutan Spreadsheet dan Slide.

4. Tugas 1: Menjalankan BigQuery dan mencatat hasil ke Spreadsheet

Pengantar

Kita akan melakukan sebagian besar codelab ini dengan tugas pertama ini. Bahkan, setelah selesai, Anda akan hampir setengah jalan menyelesaikan seluruh codelab. Dibagi menjadi beberapa subbagian, Anda akan:

• Buat Google Apps Script dan project Konsol Cloud.
• Aktifkan akses ke layanan lanjutan BigQuery.
• Buka editor skrip dan masukkan kode sumber aplikasi.
• Menavigasi proses otorisasi aplikasi (OAuth2).
• Jalankan aplikasi yang mengirimkan permintaan ke BigQuery.
• Tinjau Google Spreadsheet baru yang dibuat menggunakan hasil BigQuery.

Penyiapan

1. Untuk membuat project Apps Script, buka script.google.com, lalu klik Project baru.
2. Untuk mengganti nama project Apps Script, klik Project tanpa judul, masukkan judul untuk project Anda, lalu klik Ganti nama.

Selanjutnya, Anda harus membuat project Konsol Cloud untuk mengkueri data di BigQuery.

Catatan: Jika Anda menjalankan codelab ini dari kios di peristiwa yang dihosting Google, project Konsol Cloud sudah dibuat untuk Anda. Buka project dan lanjutkan ke langkah 5 di bawah.

3. Untuk membuat project Konsol Cloud, gunakan link pintasan ini untuk membuat project, beri nama project tersebut, lalu klik Create.

Catatan: BigQuery adalah layanan berbayar dengan biaya yang terkait untuk penyimpanan dan pembuatan kueri data. Codelab ini mengkueri sejumlah kecil data yang seharusnya termasuk dalam paket penggunaan gratis. Untuk mengetahui informasi lebih lanjut, buka panduan harga BigQuery.

4. Saat pembuatan project selesai, notifikasi akan muncul di halaman tersebut. Pastikan project baru Anda dipilih dalam daftar project di bagian atas halaman.
5. Klik Menu dan buka API & Layanan > Layar izin OAuth (link langsung).
6. Klik Internal > Buat untuk membangun aplikasi bagi pengguna Google Workspace di organisasi Anda.
7. Di kolom App name, masukkan "Big Data Codelab".
8. Masukkan email kontak untuk kolom Dukungan pengguna dan Informasi kontak developer.
9. Klik Simpan dan lanjutkan > Simpan dan lanjutkan.
10. Klik Lainnya di menu navigasi, lalu pilih Setelan project (link langsung).
11. Salin nilai yang tercantum di bagian Project number. (Kolom Project ID terpisah digunakan nanti di codelab.)

Catatan: Anda juga dapat menemukan detail project Anda di halaman Dasbor di Konsol Cloud.

Selanjutnya, Anda akan menghubungkan project Apps Script ke project Konsol Cloud.

12. Beralihlah ke editor App Script dan klik Project Settings .
13. Pada Google Cloud Platform (GCP) Project, klik Change project.
14. Masukkan nomor project dan klik Tetapkan project.
15. Selanjutnya, klik Editor untuk mulai menambahkan layanan lanjutan BigQuery.
16. Di samping Layanan, klik Tambahkan layanan .
17. Pada dialog Add a service, pilih BigQuery API, lalu klik Add.

Langkah terakhir adalah mengaktifkan BigQuery API di Konsol Cloud.

18. Untuk melakukannya, beralihlah ke Konsol Cloud dan klik APIs & Layanan > Dasbor. (Pastikan Anda masih berada di project yang sama dengan yang dibuat di Langkah 3.)
19. Klik Aktifkan API dan Layanan.
20. Telusuri "big query", pilih BigQuery API (bukan BigQuery Data Transfer API), dan klik Enable untuk mengaktifkannya.

CATATAN: Setelah API diaktifkan, Anda mungkin melihat catatan di halaman ini yang menyatakan "Untuk menggunakan API ini, Anda perlu membuat kredensial...". Jangan khawatir tentang hal ini untuk saat ini—Apps Script akan menangani langkah ini untuk Anda.

Sekarang Anda siap untuk memasukkan kode aplikasi, menjalani proses otorisasi, dan mendapatkan iterasi pertama dari aplikasi ini yang berfungsi.

Mengupload aplikasi dan menjalankannya

1. Di editor skrip, ganti blok kode myFunction() default dengan kode berikut:

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a Sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to Sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
}

2. Klik Simpan .
3. Di samping Code.gs, klik Lainnya > Ganti nama. Ubah judul dari Code.gs menjadi bq-sheets-slides.js.
4. Selanjutnya, mari kita tinjau kode yang membuat kueri BigQuery dan menulis hasilnya ke Spreadsheet Google. Anda dapat melihatnya di dekat bagian atas runQuery():

SELECT
    LOWER(word) AS word,
    SUM(word_count) AS count
FROM [bigquery-public-data:samples.shakespeare]
GROUP BY word
ORDER BY count
DESC LIMIT 10

Kueri ini menelusuri karya Shakespeare, yang merupakan bagian dari set data publik BigQuery, dan menghasilkan 10 kata yang paling sering muncul di semua karyanya, yang diurutkan dalam urutan popularitas menurun. Anda akan mendapatkan gambaran betapa bergunanya BigQuery ketika membayangkan berapa banyak pekerjaan yang bisa dilakukan jika melakukan kompilasi ini secara manual.

Fungsi tersebut juga mendeklarasikan variabel PROJECT_ID yang memerlukan project ID Konsol Cloud yang valid. Pernyataan if di bawah variabel dibuat untuk mencegah aplikasi melanjutkan tanpa project ID.

5. Beralihlah ke project Konsol Cloud Anda, klik Lainnya di menu navigasi, lalu pilih Setelan project.
6. Salin nilai yang tercantum di bagian Project ID.
7. Beralih kembali ke editor App Script, temukan variabel PROJECT_ID di bq-sheets-slides.js, lalu tambahkan nilainya.
8. Klik Simpan > Jalankan.
9. Klik Tinjau izin untuk melanjutkan.

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

1. Di dialog aplikasi yang belum diverifikasi, klik Lanjutan.
2. Klik Buka nama-project-Anda (tidak aman).
3. Klik Izinkan.

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

10. Setelah skrip mulai berjalan, log eksekusi bawaan akan terbuka dan mencatat tindakan skrip ke dalam log secara real time.

Untuk menampilkan atau menyembunyikan log eksekusi, klik Execution log di bagian atas editor.

11. Setelah log eksekusi bertuliskan "Execution completed", buka Google Drive (drive.google.com) dan temukan Spreadsheet Google bernama "Paling umum dalam semua karya Shakespeare" (atau nama yang Anda tetapkan ke variabel QUERY_NAME, jika Anda memperbaruinya):
12. Buka {i>spreadsheet<i} untuk melihat 10 kata yang paling umum dan jumlah totalnya diurutkan dalam urutan menurun:

Ringkasan tugas 1

Sebagai tinjauan, Anda menjalankan beberapa kode yang mengkueri semua karya Shakespeare yang melihat setiap kata dalam setiap permainan. Fitur ini menghitung kata dan mengurutkannya dalam urutan menurun mulai dari tampilan. Anda juga telah menggunakan layanan bawaan Apps Script untuk Google Spreadsheet guna menampilkan data ini.

Kode yang Anda gunakan untuk bq-sheets-slides.js juga dapat ditemukan di folder step1 repositori GitHub codelab ini di github.com/googlecodelabs/bigquery-sheets-slides. Kode ini terinspirasi oleh contoh asli di halaman layanan lanjutan BigQuery yang menjalankan kueri yang sedikit berbeda dan mengambil kata paling populer dengan 10 karakter atau lebih yang digunakan oleh Shakespeare. Anda juga dapat melihat contohnya di repo GitHub-nya.

Jika tertarik dengan kueri lain yang dapat Anda bangun dengan karya Shakespeare, atau tabel data publik lainnya, buka How to Query the BigQuery Sample Tables dan repo GitHub ini.

Anda juga dapat menjalankan kueri menggunakan halaman BigQuery di Konsol Cloud sebelum menjalankannya di Apps Script. Untuk menemukannya, klik Menu dan buka UI BigQuery > Ruang kerja SQL (link langsung). Misalnya, inilah tampilan kueri{i> <i}pada antarmuka grafis BigQuery:

5. Tugas 2: Membuat diagram di Google Spreadsheet

Tujuan runQuery() adalah menggunakan BigQuery dan mengirimkan hasil datanya ke Spreadsheet Google. Selanjutnya kita perlu membuat bagan dengan menggunakan data tersebut. Mari kita buat fungsi baru bernama createColumnChart() yang memanggil Spreadsheet Metode newChart().

1. Di editor Apps Script, tambahkan fungsi createColumnChart() ke bq-sheets-slides.js setelah runQuery(). Kode itu mendapatkan {i>sheet<i} dan meminta bagan kolom dengan semua data. Rentang data dimulai di sel A2 karena baris pertama berisi {i>header <i}kolom.

/**
 * Uses spreadsheet data to create a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) Sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in Sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the Sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}

2. Fungsi createColumnChart() memerlukan parameter objek spreadsheet, jadi kita perlu mengupdate runQuery() untuk menampilkan objek spreadsheet yang dapat kita teruskan ke createColumnChart(). Di akhir runQuery(), tampilkan objek spreadsheet setelah logging pembuatan sheet yang berhasil:

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());

  // NEW: Return the spreadsheet object for later use.
  return spreadsheet;
}

3. Buat fungsi createBigQueryPresentation() untuk memanggil runQuery() dan createColumnChart(). Secara logis memisahkan fungsi pembuatan diagram dan BigQuery adalah praktik terbaik:

/**
 * Runs the query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  createColumnChart(spreadsheet);
}

4. Anda melakukan 2 langkah penting di atas: mengembalikan objek {i>spreadsheet<i} dan membuat fungsi entri. Agar runQuery() lebih berguna, kita perlu memindahkan baris log dari runQuery() ke createBigQueryPresentation(). Metode Anda sekarang akan terlihat seperti ini:

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // MOVED HERE
  createColumnChart(spreadsheet);
}

Dengan perubahan di atas (kecuali untuk PROJECT_ID), bq-sheets-slides.js Anda sekarang akan terlihat seperti berikut. Kode ini juga ditemukan di step2 repo GitHub.

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create a columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

Di editor skrip, simpan project skrip Anda. Kemudian, pilih createBigQueryPresentation dari daftar fungsi lalu klik Run. Setelah selesai, {i>spreadsheet<i} lain akan dibuat di Google Drive Anda, tetapi kali ini diagram disertakan dalam sheet di sebelah data tersebut:

6. Tugas 3: Menempatkan data hasil ke dalam dek {i>slide<i}

Bagian akhir codelab ini melibatkan pembuatan presentasi Google Slide, menambahkan judul dan subjudul ke slide judul, lalu membuat slide untuk sel data dan diagram.

1. Di editor Apps Script, tambahkan fungsi createSlidePresentation() ke bq-sheets-slides.js setelah createColumnChart(). Semua pekerjaan di dek {i>slide<i} berlangsung dalam fungsi ini. Mari kita mulai dengan membuat dek {i>slide<i} dan kemudian tambahkan judul dan sub judul ke {i>slide<i} judul {i>default<i}.

/**
 * Create presentation with spreadsheet data and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
  * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('using Google Cloud Console and Google Workspace APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

2. Langkah berikutnya di createSlidePresentation() adalah mengimpor data sel dari Spreadsheet Google ke slide presentasi baru. Tambahkan cuplikan kode ini ke fungsi:

  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it with
  // the dimensions of the data range; fails if the sheet is empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }

3. Langkah terakhir di createSlidePresentation() adalah menambahkan satu slide lagi, mengimpor diagram dari spreadsheet, dan menampilkan objek Presentation. Tambahkan cuplikan kode ini ke fungsi:

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

4. Sekarang setelah fungsi selesai, lihat kembali tanda tangannya. createSlidePresentation() memerlukan parameter objek spreadsheet dan diagram. Kita telah menyesuaikan runQuery() untuk menampilkan objek Spreadsheet, tetapi kita perlu membuat perubahan serupa pada createColumnChart() agar menampilkan objek diagram (EmbeddedChart). Kembali ke createColumnChart() dan tambahkan cuplikan kode berikut ke akhir fungsi:

  // NEW: Return the chart object for later use.
  return chart;
}

5. Karena createColumnChart() sekarang menampilkan objek diagram, kita perlu menyimpan diagram ke variabel. Kemudian, kita teruskan baik spreadsheet dan variabel diagram ke createSlidePresentation(). Selain itu, karena kita mencatat URL {i>spreadsheet<i} yang baru dibuat, mari kita juga mencatat URL dari presentasi {i>slide<i} yang baru. Perbarui createBigQueryPresentation() Anda sehingga terlihat seperti ini:

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet); // UPDATED
  var deck = createSlidePresentation(spreadsheet, chart); // NEW
  Logger.log('Results slide deck created: %s', deck.getUrl()); // NEW
}

6. Simpan dan jalankan createBigQueryPresentation() lagi. Namun, sebelum dijalankan, aplikasi memerlukan satu kumpulan izin lagi dari pengguna untuk melihat dan mengelola presentasi Google Slide. Setelah Anda meninjau dan memberikan izin ini, aplikasi akan berjalan seperti sebelumnya.
7. Sekarang, selain {i>spreadsheet<i} yang dibuat, Anda juga akan mendapatkan presentasi Slide baru dengan 3 {i>slide<i} (judul, tabel data, bagan data), seperti yang ditunjukkan di bawah ini:

7. Kesimpulan

Selamat, Anda telah membuat aplikasi yang menggunakan kedua sisi Google Cloud. Ia melakukan permintaan Google BigQuery yang membuat kueri salah satu {i>dataset<i} publiknya, membuat {i>spreadsheet<i} Google Sheets untuk menyimpan hasilnya, menambahkan bagan berdasarkan data, dan akhirnya membuat presentasi Google Slides yang menampilkan data dan hasil diagram dari spreadsheet.

Langkah-langkah ini adalah apa yang Anda lakukan secara teknis. Secara umum, Anda beralih dari analisis big data ke hasil yang dapat Anda presentasikan kepada pemangku kepentingan—semuanya diotomatiskan menggunakan kode. Semoga contoh ini menginspirasi Anda untuk menyesuaikannya dengan proyek Anda sendiri. Di akhir codelab ini, kami akan memberikan beberapa saran tentang cara meningkatkan kualitas aplikasi contoh ini.

Dengan perubahan dari tugas terakhir (kecuali untuk PROJECT_ID), bq-sheets-slides.js Anda sekarang akan terlihat seperti berikut:

/**
 * Copyright 2018 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0.
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a spreadsheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
  
  // Return the chart object for later use.
  return chart;
}

/**
 * Create presentation with spreadsheet data and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
 * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('using Google Cloud Console and Google Workspace APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it with
  // the dimensions of the data range; fails if the sheet is empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet);
  var deck = createSlidePresentation(spreadsheet, chart);
  Logger.log('Results slide deck created: %s', deck.getUrl());
}

Contoh kode ini juga dapat ditemukan di folder final di repo GitHub.

8. Referensi lainnya

Berikut adalah referensi lainnya untuk membantu Anda mempelajari materi yang dibahas dalam codelab ini lebih dalam dan mengeksplorasi cara lain untuk mengakses alat developer Google secara terprogram.

Referensi untuk aplikasi ini

• Repositori kode sumber
• Video developer (dan) postingan blog
• Sesi Google Cloud NEXT ‘18

Dokumentasi

• Situs dokumentasi Google Apps Script
• Apps Script: Layanan Spreadsheet
• Apps Script: Layanan Slide
• Apps Script: Layanan lanjutan BigQuery

Video

• Rahasia Google Apps Script lainnya
• Mengakses Google Maps dari spreadsheet
• Tanpa Skrip
• Acara Developer Google Workspace

Berita & pembaruan

• Blog Google Cloud Platform
• Blog Analisis Data Google Cloud
• Blog Google Developers
• Twitter: Google Developers (@Campaigns)
• Blog Developer Google Workspace
• Twitter: Developer Google Workspace (@workspacedevs)

Codelab lainnya

Pengantar

• [Google Spreadsheet] Dasar-Dasar Apps Script dengan Google Spreadsheet
• [REST API] Gunakan Google Workspace & Google API untuk mengakses file & folder di Google Drive Anda

Menengah

• [Apps Script] Alat command line CLASP Apps Script
• [Apps Script] Add-on Google Workspace untuk Gmail
• [Apps Script] Bot kustom untuk Hangouts Chat
• [REST API] Menggunakan Google Spreadsheet sebagai alat pelaporan aplikasi Anda
• [REST API] Membuat presentasi Google Slide menggunakan BigQuery API

9. Langkah selanjutnya: tantangan kode

Di bawah ini adalah berbagai cara yang dapat Anda gunakan untuk meningkatkan kualitas atau meningkatkan contoh yang kita buat di codelab ini. Daftar ini tidak lengkap, tetapi memberikan beberapa ide inspiratif tentang bagaimana Anda dapat mengambil langkah selanjutnya.

• Aplikasi—Tidak ingin dibatasi pada penggunaan JavaScript atau pembatasan yang diberlakukan oleh Apps Script? Port aplikasi ini ke bahasa pemrograman favorit Anda yang menggunakan REST API untuk Google BigQuery, Spreadsheet, dan Slide.
• BigQuery—Lakukan eksperimen dengan berbagai kueri untuk set data Shakespeare yang Anda minati. Contoh kueri lain dapat ditemukan di aplikasi contoh BigQuery Apps Script asli.
• BigQuery—Lakukan eksperimen dengan beberapa set data publik BigQuery lainnya untuk menemukan set data yang lebih bermanfaat bagi Anda.
• BigQuery—Sebelumnya, kami menyebutkan kueri lain yang dapat Anda buat dengan karya Shakespeare, atau tabel data publik lainnya. Link tersebut dapat ditemukan di halaman web ini dan repo GitHub ini.
• Spreadsheet—Bereksperimen dengan jenis diagram lainnya di Galeri Diagram.
• Spreadsheet & BigQuery—Gunakan set data spreadsheet Anda sendiri yang besar. Pada tahun 2016, tim BigQuery memperkenalkan fitur yang memungkinkan developer menggunakan Spreadsheet sebagai sumber data. Untuk mengetahui informasi selengkapnya, buka (Google BigQuery terintegrasi dengan Google Drive.
• Slide—Menambahkan slide lain ke presentasi yang dihasilkan, seperti gambar atau aset lain yang terkait dengan analisis big data Anda. Berikut adalah dokumentasi referensi untuk layanan bawaan Slide.
• Google Workspace—Gunakan layanan Google Workspace atau layanan bawaan Google lainnya dari Apps Script. Misalnya, Gmail, Kalender, Dokumen, Drive, Maps, Analytics, YouTube, dll., serta layanan lanjutan lainnya. Untuk informasi selengkapnya, buka ringkasan referensi untuk layanan bawaan dan lanjutan.