Output Sheets as JSON in WebApp

by

The code below will output content from a Google Sheet – as JSON into a Webapp URL which is perfect to be used from any website to connect via AJAX to get the JSON data.

function doGet(e) {
  const id = '1zydy1wJynoC*****7ZFzx1t7B452UdSFqSe0E3I';
  const ss = SpreadsheetApp.openById(id);
  const sheet = ss.getSheetByName('data');
  const rows = sheet.getDataRange().getValues();
  const headers = rows[0];
  //Logger.log(headers);
  const questions = rows.slice(1);
  //Logger.log(questions);
  const holder = [];
  questions.forEach((ele, ind) => {
    const temp = {
      row: (ind + 2)
    };
    headers.forEach((header, index) => {
      header = header.toLowerCase();
      //Logger.log(header);
      //Logger.log(index);
      //Logger.log(ele);
      temp[header] = ele[index];
    })
    holder.push(temp);
  })
  const output = JSON.stringify({
    status: true,
    data: holder
  });

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

}
  1. doGet() is the default method to output webapp content
  2. Select the sheet you want to use
  3. Get the range – using getDataRange() lets you select all content from the sheet
  4. Return the values into an array getValues()
  5. slice the headers off the rest of the data rows.slice(1)
  6. array.forEach() to select each row of content array
  7. use forEach to get all the columns for the headers and place them as the property values for the new object
  8. Change to lowercase for property name
  9. Push() the new object into the holding array
  10. Use JSON stringify to turn it into a string
  11. Return the Content as JSON data in the webapp ContentService.createTextOutput(output).setMimeType(ContentService.MimeType.JSON);

JavaScript to connect to the endpoint to get the JSON data

<!DOCTYPE html>
<html>

<head>
    <title>Sheet data</title>
    <style>
        .btn {
            font-size: 1.5em;
            color: white;
            padding: 5px;
        }
    </style>
</head>

<body>
    <div class="output"></div>
    <script src="app.js"></script>
</body>

</html>

const url = 'https://script.google.com/macros/s/AKfyc****SA_iC3sFQDN-Ervz7FSKgQSfjWALeaqsfSrTR-eIMm6LmVZVrI/exec';
const output = document.querySelector('.output');
getData();

function getData() {
    fetch(url).then((res) => {
        return res.json()
    }).then((json) => {
        json.data.forEach(ele => {
            //console.log(ele);
            const div = document.createElement('div');
            div.innerHTML = `<h2>${ele.title} </h2><p>${ele.desc}</p>`;
            output.append(div);
            const btn1 = document.createElement('button');
            btn1.innerHTML = `&#9652; ${ele.up}`;
            div.append(btn1);
            btn1.style.backgroundColor = 'green';
            btn1.classList.add('btn');
            const btn2 = document.createElement('button');
            btn2.style.backgroundColor = 'red';
            btn2.classList.add('btn');
            btn2.innerHTML = `&#9662; ${ele.down}`;
            div.append(btn2);
            btn1.addEventListener('click', (e) => {
                const temp = {
                    result: 'up',
                    row: ele.row
                };
                sendData(temp, btn1);
            })
            btn2.addEventListener('click', (e) => {
                const temp = {
                    result: 'down',
                    row: ele.row
                };
                sendData(temp, btn2);
            })
        });
    })

}

function sendData(obj, ele) {
    console.log(obj);
    let formData = new FormData();
    formData.append('data', JSON.stringify(obj));
    fetch(url, {
        method: "POST",
        body: formData
    }).then((rep) => {
        return rep.json()
    }).then((json) => {
        console.log(json);
        let temp = ele.textContent.slice(0, 2);
        console.log(temp);
        ele.innerHTML = temp + json.val;
    })
}

POST to Google Sheet and update sheet by range –

doPost() method

function doPost(e) {
  const newVal = updateSheet(e.parameter.data);
  const output = { result: 'success', data: e.parameter.data, val: newVal };
  return ContentService.createTextOutput(JSON.stringify(output)).setMimeType(ContentService.MimeType.JSON);
}

function updateSheet(data) {
  data = JSON.parse(data);
  const id = '1zydy1wJynoC5BtE04QU_7ZFzx1t7B452UdSFqSe0E3I';
  const sheet = SpreadsheetApp.openById(id).getSheetByName('data');
  const col = data.result == "up" ? 3 : 4;
  const row = data.row;
  const range = sheet.getRange(row, col);
  let val = Number(range.getValue());
  val++;
  range.setValue(val);
  return val;
}

Resource for setting up live server

https://code.visualstudio.com/

https://www.npmjs.com/package/live-server

https://developer.mozilla.org/en-US/docs/Web/API/Fetch_API

Leave a Comment