Bagaimana cara menambahkan data dari api ke google sheets?

Terlalu panjang;

Koin Disebutkan

Selain itu, Coupler. io menyediakan integrasi JSON yang memungkinkan Anda mengintegrasikan Google Sheets dengan berbagai platform, seperti Salesforce, Typeform, Help Scout, dan lainnya. Satu-satunya prasyarat adalah menyediakan REST API.  

Dapatkan data dari API tanpa coding

Anda tidak perlu menjadi programmer untuk mengonfigurasi dan menguasai integrasi JSON. Namun, menggunakan API memerlukan pengetahuan teknis, jadi bersiaplah untuk bekerja dengan dokumentasi API dari sumber data JSON. Setiap API mungkin memerlukan solusi khusus, seperti menangani autentikasi, paginasi, batas kecepatan, dan sebagainya.  

Jika Anda tidak memiliki latar belakang teknis, jangan khawatir. Artikel ini akan menjelaskan poin utama yang perlu Anda perhitungkan saat menghubungkan API ke Google Sheets

Cara menarik data API ke Google Sheets dengan Coupler. io

Daftar ke Coupler. io dengan akun Google Anda

Catatan. Sebagai alternatif, Anda dapat menginstal Coupler. .io untuk Google Spreadsheet dari Google Workspace Marketplace, lalu lakukan penyiapan langsung di spreadsheet Anda

Klik tombol “Tambah importir baru”, beri label pada importir Anda, lalu selesaikan ketiga langkah ini. sumber, tujuan, dan jadwal

Sumber

• Pilih JSON sebagai aplikasi sumber dari daftar. Klik Lanjutkan

• Masukkan string URL JSON – ini adalah URL API + titik akhir tempat permintaan HTTP dikirim. Endpoint adalah postfix URL yang berbeda bergantung pada jenis data yang dimuat dari API. Anda dapat menemukan URL JSON di dokumentasi RESTful API dari sumber data Anda. Misalnya, URL JSON yang digunakan untuk mendapatkan daftar dokumen dari Coda adalah sebagai berikut

https://coda.io/apis/v1/docs

• Klik Lanjutkan untuk menyiapkan Setelan Lanjutan untuk Sumber.

• Metode HTTP – Anda dapat memilih metode HTTP untuk membuat permintaan berdasarkan dokumentasi platform sumber data Anda. GET adalah metode default
• Header HTTP – Anda dapat menerapkan header HTTP khusus untuk permintaan Anda. Misalnya, header Authorization memungkinkan Anda menentukan kredensial yang diperlukan untuk membuat permintaan HTTP. Seperti inilah tampilannya untuk Coda

Authorization: Bearer {API-token}

• String kueri URL – Anda dapat menggunakan parameter filter jika dikaitkan dengan URL JSON API. Misalnya, berikut adalah string kueri URL untuk memfilter daftar percakapan menurut kotak surat dan status di Help Scout API

mailbox: {mailbox_id}
status: open

• Badan permintaan – jika metode permintaan Anda adalah POST, PUT, PATCH atau DELETE, Anda dapat menambahkan data ke permintaan Anda untuk dikirim ke API. Anda dapat melihat tampilannya di postingan blog kami. Posting Pesan ke Slack dari Google Sheets
• Bidang – Anda dapat menentukan bidang (kolom) yang akan diimpor ke spreadsheet Anda. Misalnya,

customer_firstname, customer_lastname, customer_email, status

• Path – Anda dapat memilih objek bersarang dari respons JSON. Dalam kebanyakan kasus, ini memungkinkan Anda untuk mengecualikan informasi yang tidak diperlukan dalam data yang diekspor dari API. Misalnya, dengan menggunakan parameter Path berikut, data dari Help Scout akan ditempatkan dalam beberapa baris

_embedded.mailboxes

Catatan. Anda dapat menemukan informasi tentang parameter yang digunakan dalam bidang tersebut dalam dokumentasi API aplikasi Anda. Perhatikan informasi tentang autentikasi API dan detail titik akhir API – ini biasanya terletak di bagian terpisah.  

Setelah Anda siap dengan pengaturan Sumber, Lompat ke Pengaturan Tujuan.  

Tujuan

• Pilih Google Sheets sebagai aplikasi tujuan tempat Anda ingin menyimpan data, lalu sambungkan ke akun Google Anda. Klik Lanjutkan

Alternatifnya, Anda dapat memilih Microsoft Excel jika Anda perlu menghubungkan API ke Excel

• Pilih file Google Sheets di Google Drive Anda yang akan menjadi tujuan data yang ditransfer. Pilih lembar yang ada atau masukkan nama untuk membuat yang baru. Klik Lanjutkan

• Jika Anda ingin mengubah sel pertama untuk rentang data yang diimpor, tentukan nilai Anda di bidang Alamat sel. Sel A1 diatur secara default.  
• Pilih mode impor untuk data Anda. Anda dapat mengganti informasi sebelumnya atau menambahkan baris baru di bawah entri yang paling baru diimpor

• Aktifkan fitur kolom Terakhir diperbarui jika Anda ingin menambahkan kolom ke spreadsheet yang berisi tanggal dan waktu penyegaran terakhir

Jadwal

Jika Anda ingin mengotomatiskan impor data sesuai jadwal, aktifkan Penyegaran data otomatis dan sesuaikan jadwal

• Pilih Interval dari 15 menit hingga sekali per bulan
• Pilih Hari dalam seminggu
• Pilih Preferensi waktu
• Jadwalkan zona waktu

Setelah Anda menyiapkan koneksi API ke Google Sheets, klik Simpan dan Jalankan untuk mendapatkan data ke spreadsheet Anda. Mari kita lihat cara kerjanya dalam sebuah contoh

Contoh cara menambahkan JSON API ke Google Sheets

Kami akan mengimpor formulir dari Typeform. Pertama kita harus membaca dokumentasi API Typeform. URL JSON untuk meminta semua respons formulir dari suatu bentuk huruf adalah sebagai berikut

https://api.typeform.com/forms/

Untuk permintaan HTTP ke Typeform API, kita perlu menggunakan header HTTP Authorization.  

Authorization: Bearer {your_access_token}

Catatan. Anda dapat membaca tentang bagaimana dan di mana mendapatkan token akses pribadi Typeform Anda di panduan kami

Berikut adalah tampilan parameter Sumber dari integrasi JSON

Klik Simpan & Jalankan dan sambut data JSON Anda dari Typeform API ke Google Sheets

Kami mendorong Anda untuk membaca artikel lain yang menampilkan integrasi JSON untuk mendapatkan data dari API ke Google Sheets

• Cara mengekspor pesanan Magento ke Google Sheets

Cara menggunakan API eksternal di Google Sheets menggunakan kode

Di atas, kami telah menambahkan JSON API ke Google Sheets tanpa sederet kode, bukan? . Kami akan menjelajahi solusi yang paling jelas berdasarkan Google Apps Script di Google Sheets

Bagaimana cara menarik data API ke Google Sheets dengan Apps Script?

Ide dari pendekatan ini adalah untuk membuat fungsi Google Sheets khusus yang akan mengambil dan mengonversi data JSON baik secara manual maupun otomatis

Buka dokumen Google Sheets Anda dan buka Tools => Script editor

Tambahkan kode berikut yang dibuat oleh Brad Jasper dan Trevor Lohrbeer ke Editor Skrip, beri nama proyek Anda dan klik Simpan

function ImportJSON(url, query, parseOptions) {
  return ImportJSONAdvanced(url, null, query, parseOptions, includeXPath_, defaultTransform_);
}
 
function ImportJSONViaPost(url, payload, fetchOptions, query, parseOptions) {
  var postOptions = parseToObject_(fetchOptions);
  
  if (postOptions["method"] == null) {
    postOptions["method"] = "POST";
  }
 
  if (postOptions["payload"] == null) {
    postOptions["payload"] = payload;
  }
 
  if (postOptions["contentType"] == null) {
    postOptions["contentType"] = "application/x-www-form-urlencoded";
  }
 
  convertToBool_(postOptions, "validateHttpsCertificates");
  convertToBool_(postOptions, "useIntranet");
  convertToBool_(postOptions, "followRedirects");
  convertToBool_(postOptions, "muteHttpExceptions");
  
  return ImportJSONAdvanced(url, postOptions, query, parseOptions, includeXPath_, defaultTransform_);
}
 
function ImportJSONFromSheet(sheetName, query, options) {
 
  var object = getDataFromNamedSheet_(sheetName);
  
  return parseJSONObject_(object, query, options, includeXPath_, defaultTransform_);
}
 
function ImportJSONAdvanced(url, fetchOptions, query, parseOptions, includeFunc, transformFunc) {
  var jsondata = UrlFetchApp.fetch(url, fetchOptions);
  var object   = JSON.parse(jsondata.getContentText());
  
  return parseJSONObject_(object, query, parseOptions, includeFunc, transformFunc);
}
 
function ImportJSONBasicAuth(url, username, password, query, parseOptions) {
  var encodedAuthInformation = Utilities.base64Encode(username + ":" + password);
  var header = {headers: {Authorization: "Basic " + encodedAuthInformation}};
  return ImportJSONAdvanced(url, header, query, parseOptions, includeXPath_, defaultTransform_);
}
 
function URLEncode(value) {
  return encodeURIComponent(value.toString());  
}
 
function AddOAuthService__(name, accessTokenUrl, requestTokenUrl, authorizationUrl, consumerKey, consumerSecret, method, paramLocation) {
  var oAuthConfig = UrlFetchApp.addOAuthService(name);
 
  if (accessTokenUrl != null && accessTokenUrl.length > 0) {
    oAuthConfig.setAccessTokenUrl(accessTokenUrl);
  }
  
  if (requestTokenUrl != null && requestTokenUrl.length > 0) {
    oAuthConfig.setRequestTokenUrl(requestTokenUrl);
  }
  
  if (authorizationUrl != null && authorizationUrl.length > 0) {
    oAuthConfig.setAuthorizationUrl(authorizationUrl);
  }
  
  if (consumerKey != null && consumerKey.length > 0) {
    oAuthConfig.setConsumerKey(consumerKey);
  }
  
  if (consumerSecret != null && consumerSecret.length > 0) {
    oAuthConfig.setConsumerSecret(consumerSecret);
  }
  
  if (method != null && method.length > 0) {
    oAuthConfig.setMethod(method);
  }
  
  if (paramLocation != null && paramLocation.length > 0) {
    oAuthConfig.setParamLocation(paramLocation);
  }
}
 
function parseJSONObject_(object, query, options, includeFunc, transformFunc) {
  var headers = new Array();
  var data    = new Array();
  
  if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) {
    query = query.toString().split(",");
  }
 
  // Prepopulate the headers to lock in their order
  if (hasOption_(options, "allHeaders") && Array.isArray(query))
  {
    for (var i = 0; i < query.length; i++)
    {
      headers[query[i]] = Object.keys(headers).length;
    }
  }
  
  if (options) {
    options = options.toString().split(",");
  }
    
  parseData_(headers, data, "", {rowIndex: 1}, object, query, options, includeFunc);
  parseHeaders_(headers, data);
  transformData_(data, options, transformFunc);
  
  return hasOption_(options, "noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data;
}
 
function parseData_(headers, data, path, state, value, query, options, includeFunc) {
  var dataInserted = false;
 
  if (Array.isArray(value) && isObjectArray_(value)) {
    for (var i = 0; i < value.length; i++) {
      if (parseData_(headers, data, path, state, value[i], query, options, includeFunc)) {
        dataInserted = true;
 
        if (data[state.rowIndex]) {
          state.rowIndex++;
        }
      }
    }
  } else if (isObject_(value)) {
    for (key in value) {
      if (parseData_(headers, data, path + "/" + key, state, value[key], query, options, includeFunc)) {
        dataInserted = true; 
      }
    }
  } else if (!includeFunc || includeFunc(query, path, options)) {
    // Handle arrays containing only scalar values
    if (Array.isArray(value)) {
      value = value.join(); 
    }
    
    if (!data[state.rowIndex]) {
      data[state.rowIndex] = new Array();
    }
    
    if (!headers[path] && headers[path] != 0) {
      headers[path] = Object.keys(headers).length;
    }
    
    data[state.rowIndex][headers[path]] = value;
    dataInserted = true;
  }
  
  return dataInserted;
}
 
function parseHeaders_(headers, data) {
  data[0] = new Array();
 
  for (key in headers) {
    data[0][headers[key]] = key;
  }
}
 
function transformData_(data, options, transformFunc) {
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[0].length; j++) {
      transformFunc(data, i, j, options);
    }
  }
}
 
function isObject_(test) {
  return Object.prototype.toString.call(test) === '[object Object]';
}
 
function isObjectArray_(test) {
  for (var i = 0; i < test.length; i++) {
    if (isObject_(test[i])) {
      return true; 
    }
  }  
 
  return false;
}
 
function includeXPath_(query, path, options) {
  if (!query) {
    return true; 
  } else if (Array.isArray(query)) {
    for (var i = 0; i < query.length; i++) {
      if (applyXPathRule_(query[i], path, options)) {
        return true; 
      }
    }  
  } else {
    return applyXPathRule_(query, path, options);
  }
  
  return false; 
};
 
function applyXPathRule_(rule, path, options) {
  return path.indexOf(rule) == 0; 
}
 
function defaultTransform_(data, row, column, options) {
  if (data[row][column] == null) {
    if (row < 2 || hasOption_(options, "noInherit")) {
      data[row][column] = "";
    } else {
      data[row][column] = data[row-1][column];
    }
  } 
 
  if (!hasOption_(options, "rawHeaders") && row == 0) {
    if (column == 0 && data[row].length > 1) {
      removeCommonPrefixes_(data, row);  
    }
    
    data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g, " "));
  }
  
  if (!hasOption_(options, "noTruncate") && data[row][column]) {
    data[row][column] = data[row][column].toString().substr(0, 256);
  }
 
  if (hasOption_(options, "debugLocation")) {
    data[row][column] = "[" + row + "," + column + "]" + data[row][column];
  }
}
 
function removeCommonPrefixes_(data, row) {
  var matchIndex = data[row][0].length;
 
  for (var i = 1; i < data[row].length; i++) {
    matchIndex = findEqualityEndpoint_(data[row][i-1], data[row][i], matchIndex);
 
    if (matchIndex == 0) {
      return;
    }
  }
  
  for (var i = 0; i < data[row].length; i++) {
    data[row][i] = data[row][i].substring(matchIndex, data[row][i].length);
  }
}
 
function findEqualityEndpoint_(string1, string2, stopAt) {
  if (!string1 || !string2) {
    return -1; 
  }
  
  var maxEndpoint = Math.min(stopAt, string1.length, string2.length);
  
  for (var i = 0; i < maxEndpoint; i++) {
    if (string1.charAt(i) != string2.charAt(i)) {
      return i;
    }
  }
  
  return maxEndpoint;
}
  
 
function toTitleCase_(text) {
  if (text == null) {
    return null;
  }
  
  return text.replace(/\w\S*/g, function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); });
}
 
function hasOption_(options, option) {
  return options && options.indexOf(option) >= 0;
}
 
function parseToObject_(text) {
  var map     = new Object();
  var entries = (text != null && text.trim().length > 0) ? text.toString().split(",") : new Array();
  
  for (var i = 0; i < entries.length; i++) {
    addToMap_(map, entries[i]);  
  }
  
  return map;
}
 
function addToMap_(map, entry) {
  var equalsIndex = entry.indexOf("=");  
  var key         = (equalsIndex != -1) ? entry.substring(0, equalsIndex) : entry;
  var value       = (key.length + 1 < entry.length) ? entry.substring(key.length + 1) : "";
  
  map[key.trim()] = value;
}
 
function toBool_(value) {
  return value == null ? false : (value.toString().toLowerCase() == "true" ? true : false);
}
 
function convertToBool_(map, key) {
  if (map[key] != null) {
    map[key] = toBool_(map[key]);
  }  
}
 
function getDataFromNamedSheet_(sheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheetByName(sheetName);
  
  var jsonRange = source.getRange(1,1,source.getLastRow());
  var jsonValues = jsonRange.getValues();
  
  var jsonText = "";
  for (var row in jsonValues) {
    for (var col in jsonValues[row]) {
      jsonText +=jsonValues[row][col];
    }
  }
  Logger.log(jsonText);
  return JSON.parse(jsonText);
}

Skrip Aplikasi ini mengakumulasikan beberapa fungsi bagi Anda untuk mengimpor JSON dari API ke Google Sheets

• Authorization: Bearer {API-token}

  _2 – untuk mengimpor JSON dari URL API

• Authorization: Bearer {API-token}

  _3 – untuk mengimpor JSON dari salah satu Spreadsheet

• Authorization: Bearer {API-token}

  4 – untuk mengimpor JSON dari URL API menggunakan parameter POST

• Authorization: Bearer {API-token}

  5 – untuk mengimpor JSON dari URL API dengan HTTP Basic Auth

• Authorization: Bearer {API-token}

  _6 – untuk mengimpor JSON menggunakan parameter lanjutan

Pelajari lebih lanjut tentang skrip di Brad's Github.  

Fungsi khusus ini bekerja dengan cara yang sama seperti kebanyakan fungsi Google Sheets. Misalnya, berikut adalah sintaks dari

Authorization: Bearer {API-token}

=ImportJSON(url, query, parseOptions)

• Authorization: Bearer {API-token}

  _8 adalah URL API ke file JSON

• Authorization: Bearer {API-token}

  _9 adalah daftar jalur yang akan diimpor yang dipisahkan koma (parameter opsional)

• mailbox: {mailbox_id}
  status: open

  0 adalah daftar opsi yang dipisahkan koma yang mengubah pemrosesan data (parameter opsional)

Dan inilah cara kerjanya dalam aksi. Kami telah menggunakan fungsi untuk mengimpor nilai tukar mata uang asing saat ini dari API nilai tukar

=importjson("http://api.exchangeratesapi.io/v1/latest?access_key={your-access-key}") 

Authorization: Bearer {API-token}

Apps Script untuk mengunggah JSON ke Google Sheets menggunakan token API

Tambahkan cuplikan kode berikut ke skrip di Editor Skrip Anda dan simpan proyek

Authorization: Bearer {API-token}

Ini membuat fungsi baru bernama

mailbox: {mailbox_id}
status: open

• URL API JSON
• Token API dalam format.

  mailbox: {mailbox_id}
  status: open

  _3

Lihat cara kerjanya

Authorization: Bearer {API-token}

Lihat bagaimana kami menggunakan ini untuk mengimpor data dari GitHub ke Google Spreadsheet.

Hubungkan API ke Google Sheets sesuai jadwal

Di Editor Skrip, Anda dapat menyiapkan pemicu berdasarkan waktu untuk menjalankan fungsi kustom Anda. Untuk melakukan ini, buka Pemicu

Tambahkan pemicu untuk mengotomatiskan impor data dari API ke Google Sheets. Kami menjelaskan secara rinci bagaimana Anda dapat melakukan ini di tutorial tentang

Hubungkan spreadsheet Anda ke API JSON hanya dengan beberapa klik

API ke Google Sheets dengan atau tanpa coding?

Kami menunjukkan bahwa Anda dapat menghubungkan API ke Google Sheets hanya dengan beberapa klik dan beberapa penelitian. Tentu saja, ini bukan jalan-jalan di taman, tapi juga bukan ilmu roket. Alat prerangkai. io memberi Anda integrasi JSON yang menarik data dari sebagian besar API JSON tanpa perlu pengkodean. Namun, ini bukan solusi siap pakai seperti Xero ke Google Sheets. Jika Anda menginvestasikan upaya ekstra untuk menguasainya, itu akan memberi Anda kembali dua kali lipat. Semoga berhasil dengan data Anda

Bagaimana cara mengimpor data API ke Google Sheets?

Bisakah Google Spreadsheet berinteraksi dengan API?

Bagaimana cara mengakses API di Google Sheets?

Bagaimana cara mengimpor data JSON ke Google Sheets?