how to create google sheet and fetch data json

Create the Google Sheet

First, let’s set up a well-structured product data sheet:

Column Headers (Row 1):

• Product ID
• Product Name
• Category
• Price
• Stock
• Description
  

  Make Sheet Publicly Accessible

  1. Click Share button (top right)
  2. Click Change to anyone with the link
  3. Set permission to Viewer
  4. Copy the sheet URL

  3. Get Your Sheet ID

  From the URL: https://spreadsheets.google.com/feeds/list/<sheet id>/public/values?alt=json

  Copy the YOUR_SHEET_ID part.

  4. Fetch Data in JSON Format

  There are several methods:

• Let me help you fetch this data in JSON format.

  Your Sheet ID is: 10THLmyDkf-RVY7p1PQc-qXTSr8r76crX8lM6rVECMFs

  Quickest Solution: Using Apps Script

  Follow these steps:

  Step 1: Open Apps Script

  1. In your Google Sheet, click Extensions → Apps Script
  2. Delete any existing code
  3. Paste this code:

function doGet(e) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Sheet1’);
const data = sheet.getDataRange().getValues();

// First row contains headers
const headers = data[0];
const rows = data.slice(1);

// Filter out empty rows
const jsonData = rows
.filter(row => row.some(cell => cell !== ”))
.map(row => {
const obj = {};
headers.forEach((header, index) => {
obj[header] = row[index] || ”;
});
return obj;
});

return ContentService.createTextOutput(JSON.stringify(jsonData, null, 2))
.setMimeType(ContentService.MimeType.JSON);
}

Step 2: Deploy as Web App

1. 1. Click the Deploy button (top right) → New deployment
   2. Click the gear icon ⚙️ next to “Select type” → Choose Web app
   3. Fill in:
      • Description: Product API
      • Execute as: Me
      • Who has access: Anyone
   4. Click Deploy
   5. Authorize the script (click Review Permissions → Choose your account → Allow)
   6. Copy the Web App URL (it will look like: https://script.google.com/macros/s/ABC123.../exec)

 if authorization dialog. This is normal when deploying an Apps Script. Here’s what to do:

Step-by-Step Authorization Process:

1. Click “Review permissions” button

2. Choose Your Google Account

Select the account that owns the Google Sheet

3. You’ll See a Warning Screen

Google will show: “Google hasn’t verified this app”

Don’t worry! This is your own script, so it’s safe.

4. Click “Advanced” (bottom left)

5. Click “Go to Untitled project (unsafe)”

(It says “unsafe” because it’s not verified by Google, but it’s YOUR script)

6. Click “Allow”

Grant permissions to access your Google Sheets

7. Copy the Web App URL

After authorization, you’ll see a URL like:

https://script.google.com/macros/s/AKfycby.../exec