How to get json array from spreadsheet using Apps Script

Question

I tried to get all the data in JSON array from a spreadsheet using Google Apps Script but the result is always null alias nothing blank.

enter image description here

I made this function to search for data with the record "NIK" or "NKK '. Please correct it if it's wrong

    function getVoterByNIK(request, sheet) {
      var output = ContentService.createTextOutput(), result = {};

      var nkk = request.parameter.nkk
      var nik = request.parameter.nik

  var searchBy;
  var values; 

  if(request.parameter.nik){
    searchBy = nik
    values = sheet.getRange(2,2,sheet.getLastRow(),sheet.getLastColumn()).getValues();
  } 
  if(request.parameter.nkk){
    searchBy = nkk
    values = sheet.getRange(2,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
  }
 
  for(var i = 0; i < values.length; i++) {
    if(values[i][0] == searchBy ){
      i=i+2;
            
      var nkk = sheet.getRange(i,1).getValue();
      var nik = sheet.getRange(i,2).getValue();
      var nama = sheet.getRange(i,3).getValue();
      var tempat_lahir = sheet.getRange(i,4).getValue();
      var tgl_lahir = sheet.getRange(i,5).getValue();
      var status_perkawinan = sheet.getRange(i,6).getValue();
      var jk = sheet.getRange(i,7).getValue();
      var alamat = sheet.getRange(i,8).getValue();
      var rt = sheet.getRange(i,9).getValue();
      var rw = sheet.getRange(i,10).getValue();
      var disabilitas = sheet.getRange(i,11).getValue();
      var status_perekaman = sheet.getRange(i,12).getValue();
      var keterangan = sheet.getRange(i,13).getValue();
      var tps = sheet.getRange(i,14).getValue();
      var kehadiran = sheet.getRange(i,15).getValue();

      result = JSON.stringify({
                    "voters": {
                        "nkk":nkk,
                        "nik":nik,
                        "nama":nama,
                        "tempat_lahir":tempat_lahir,
                        "tgl_lahir":tgl_lahir,
                        "status_perkawinan":status_perkawinan,
                        "jk":jk,
                        "alamat":alamat,
                        "rt":rt,
                        "rw":rw,
                        "disabilitas":disabilitas,
                        "status_perekaman":status_perekaman,
                        "keterangan":keterangan,
                        "tps":tps,
                        "kehadiran":kehadiran
                        }});
        if(result.length != ""){
        result["error"] = false;
        result["code"] = 200;
        result["message"] = "Data ditemukan";
        output.setContent(JSON.stringify(result));
        output.setMimeType(ContentService.MimeType.JAVASCRIPT);
      }else{
        result["error"] = true;
        result["code"] = 404;
        result["message"] = "NKK atau NIK tidak ditemukan";
        output.setContent(JSON.stringify(result));
        output.setMimeType(ContentService.MimeType.JAVASCRIPT);
      }
    }
  }
  return output;
}

I really appreciate who helped me in solving this

oshliaer · Accepted Answer · 2021-02-23 05:44:37Z

Your code works for me fine. I just added

function doGet(e) {
  const sheet = SpreadsheetApp.openById('ABCD1234')
    .getSheets()[0];
  return getVoterByNIK(e, sheet)
}

Then I call via my REST client

GET https://script.google.com/macros/s/VBNM_76544/exec?nkk=asdf56 HTTP/1.1
Content-Type: application/json

It returns

HTTP/1.1 200 OK
Content-Type: text/javascript; charset=utf-8
Access-Control-Allow-Origin: *
Cache-Control: no-cache, no-store, max-age=0, must-revalidate
Pragma: no-cache
Expires: Mon, 01 Jan 1990 00:00:00 GMT
Date: Tue, 23 Feb 2021 04:34:41 GMT
Content-Encoding: gzip
X-Content-Type-Options: nosniff
X-Frame-Options: SAMEORIGIN
Content-Security-Policy: frame-ancestors 'self'
X-XSS-Protection: 1; mode=block
Server: GSE
Alt-Svc: h3-29=":443"; ma=2592000,h3-T051=":443"; ma=2592000,h3-Q050=":443"; ma=2592000,h3-Q046=":443"; ma=2592000,h3-Q043=":443"; ma=2592000,quic=":443"; ma=2592000; v="46,43"
Connection: close
Transfer-Encoding: chunked

"{\"voters\":{\"nkk\":\"asdf56\",\"nik\":\"asdf\",\"nama\":\"asdf\",\"tempat_lahir\":\"asdf\",\"tgl_lahir\":\"asdf\",\"status_perkawinan\":\"asdf\",\"jk\":\"asdf\",\"alamat\":\"asdf\",\"rt\":\"asdf\",\"rw\":\"asdf\",\"disabilitas\":\"asdf\",\"status_perekaman\":\"asdf\",\"keterangan\":\"\",\"tps\":\"\",\"kehadiran\":\"\"}}"

My sample data

Recommendations:

Don't forget re-publish your app
Check the data types in the Sheet. Perhaps there are some mistakes
Wrap everything in try catch and write to the log

function doGet(e) {
  try {
    const sheet = SpreadsheetApp.openById('ABCD1234')
      .getSheets()[0];
    return getVoterByNIK(e, sheet)
  } catch (err) {
    SpreadsheetApp.openById('LOG_SHEET_ID')
      .appendRow([new Date(), err.stack, err.message])
  }
}

Collectives™ on Stack Overflow

How to get json array from spreadsheet using Apps Script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related