CODEWITHSUNDEEP
webgoogle-apps-scriptgoogle-sheetswebapp2
light1
light1

Reputation: 184

Web App accessing data from private sheet

enter image description here I have created a webpage (using HTML, CSS and javascript). I have a google sheet that stores data. I want to fetch data from google sheet to this web page and wants to display that. I am able to this task if the google sheet is public. But I don't know how to do it if the google sheet is private. I have attached an image as well which displays the error when we try to fetch data from a private sheet.

This is a google script part.

function doGet() {
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("Sheet1");
  const data = ws.getRange("A1").getDataRegion().getValues();
  const headers = data.shift();

  const jsonArray = data.map(r => {
    
    let obj = {};
    headers.forEach((h, i) => {
      obj[h] = r[i];
    })
    return obj;
  });

  const response = [{status: 200, data: jsonArray}];

  return sendJSON(response);
}

This is a javascript part.

<!DOCTYPE HTML>
<html lang="en">
<head>
 <meta charset="UTF-8">
 <meta http-equiv="X-UA-Compatible" content="IE=edge">
 <meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
</head>
<body>
  
<button id="button">Fetch</button>

<script>
 async function testGS() {
   const url="https://script.google.com/macros/s/AKfycbzn5SQrQDY6gIPfVrsYI4Vy4nPf9O_7Omr_-ehgBv0SabSYSGrbzGGpJZRD7iAkTxfF/exec"
   await fetch(url)
    .then(d => d.json())
    .then(d => {
      console.log(d);
    });
}

document.getElementById("button").addEventListener("click", 
function() {
  testGS();
});
  </script>
  
  </body>
  </html>

Upvotes: 1

Views: 488

Answers (1)

Jacques-Guzel Heron
Jacques-Guzel Heron

Reputation: 2598

After reading your question and its comments I understand that you developed a web-app that fetches data from a Sheet. Anybody can run the web-app, and it access the data as the user accessing the web-app. Furthermore the Sheet is owned by a different user to keep it private. In that case, you can't access the Sheet with your web-app because the Sheet is private. I strongly recommend you to ask the Sheet owner to change it status in one of the following ways:

  1. Update the Sheet from private to public.
  2. Share the Sheet with you and update the web-app to access the data as your account.

Please write back in this answer if you need help configuring one of the workarounds.

Upvotes: 0

Related Questions