menampilkan API google dengan google sheet

materi kali ini adalah menampilkan API google dengan google sheet , kita buat satu sample sheet sebagai berikut : https://docs.google.com/spreadsheets/d/1qLtDb1IH35woz2qk_IWwAxoKTlT4jLR5GghJVyH3FMM/edit?ts=5ee83093#gid=1966830543

nah kita akan menampilkan data sheet tersebut dengan menggunkan google script. kodenya sebagai berikut :

code.js  :

function doGet(e){

 var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1qLtDb1IH35woz2qk_IWwAxoKTlT4jLR5GghJVyH3FMM/edit#gid=1966830543");
 var sheet = ss.getSheetByName("data2"); 
 return getUsers(sheet);
 
}


function getUsers(sheet){
  var jo = {};
  var dataArray = [];
  var rows = sheet.getRange(2,1,sheet.getLastRow()-1, sheet.getLastColumn()).getValues();
 
  for(var i = 0, l= rows.length; i<l ; i++){
    var dataRow = rows[i];
    var record = {};
    record['id'] = dataRow[0];
    record['name'] = dataRow[1];
    record['email'] = dataRow[2];
    dataArray.push(record);
   
  } 
 
  jo.user = dataArray;
 
  var result = JSON.stringify(jo);
 
  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
 
} 

dari kode di atas akan menapilkan data json dari url berikut : 

https://script.googleusercontent.com/macros/echo?user_content_key=k1icigpSJSkyHsD7kujDp10H9Nq0PZ71l1FHMSUnzSW5dByJWp4n4X2YfivVviWsy185mzRsIu8uBxFHXpfEoDu3zk3hhSEnm5_BxDlH2jW0nuo2oDemN9CCS2h10ox_1xSncGQajx_ryfhECjZEnDLMDx2aDDjNDKsfBxIhcMtfToULCX94Tc3pdy9bFsiGjrKQoTQi29iiCJvF3XCchog3nbldWvo5&lib=MlPEAd3Jf5XdLxrCVm9kzNHJAXLFnk5gG

nah selanjutkan kita akan menampilkan detail dari api tersebut : 

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1qLtDb1IH35woz2qk_IWwAxoKTlT4jLR5GghJVyH3FMM/edit?ts=5ee83093#gid=1966830543");

var sheet = ss.getSheetByName("data2");

function doGet(e){

 return search(e) ;

}  

function doPost(e){

 return search(e) ;

}  

function search(e){

 var id = e.parameter.id;

  

  var jo = {};

 

  var dataArray = [];

  

  var values = sheet.getRange(2, 1, sheet.getLastRow(),sheet.getLastColumn()).getValues();

  

  for(var i = 0;i<values.length; i++){

    

    if(values[i][0] == id ){

      

      var record = {};

      record['id'] = values[i][0];

      record['name'] = values[i][1];

      record['email'] = values[i][2];

      dataArray.push(record);

      

    }

  }

  

  jo.user = dataArray;

  

  var result = JSON.stringify(jo);

  

  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);

 

  

}

dari data di atas hasillnya akan menampilkan detail dari masing masing api .

berikut url api datanya.

https://script.google.com/macros/s/AKfycbyTIrz4S3gk62craftdkDO-t5EZ4kxKE38nF_vigtLnmh-eXrsQ/exec?id=LPKS%20IKHTIAR%20BERSAMA

Read data from database using fetch javascript

this data from : https://www.baznaspayakumbuh.com/api/api_tabloit_pulsa.php

and we will fetch data from api to HTML .

The following code is :

<script>

  fetch('https://www.baznaspayakumbuh.com/api/api_tabloit_pulsa.php')

  .then(response => response.json())

  .then(data => {

                      let output = '<h2>Lists of Users</h2>';

                        output += '<ul>';

                          for(let i=0 ; i < data.result.length; i++){

                            output += `<li> ${data.result[i].title}</li>`;

                          }

                        output += '</ul>';

              document.getElementById("response").innerHTML = output;

  });

</script>

<div id="response"></div>

Happy Code ...