Client Side Code creation and Apps Script to get Sheet data to web app Client Side JavaScript

by

Instead of making an additional fetch request from the client-side to get the data, you will pass the data directly when the HTML page is initially served. Here’s how you can achieve this:

Google Apps Script: Server-Side

You’ll modify the doGet function to use an HTML template that includes the data. This allows the server to pre-populate the HTML with data before it’s sent to the client.

  1. Create the Apps Script:
    • Open Google Sheets and select “Extensions” -> “Apps Script”.
    • Replace any existing code with the following:
function doGet() {
  const template = HtmlService.createTemplateFromFile('Index');
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  template.data = JSON.stringify(data);
  
  return template.evaluate();
}

function setup() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  sheet.getRange("A1").setValue("Name");
  sheet.getRange("A2").setValue("Alice");
  sheet.getRange("A3").setValue("Bob");
  // Add more initial data as needed
}
  1. Add an HTML File:
    • In the Apps Script editor, click on “File” -> “New” -> “Html file” and name it Index.
    • Insert the following HTML and scriptlet code:
<!DOCTYPE html>
<html>
<head>
    <title>Spreadsheet Data</title>
</head>
<body>
    <h1>Spreadsheet Data</h1>
    <pre id="output"><?= data ?></pre>

    <script>
        // Additional client-side JavaScript can go here.
    </script>
</body>
</html>

Deploying the Web App

  • After adding the HTML and script code, click on “Deploy” -> “New deployment”.
  • Set the access to “Anyone” and deploy.
  • This will give you a URL for your web app.

How This Works

  • Server-Side (doGet): The doGet function reads data from the active Google Sheet, converts it into a JSON string, and injects this directly into the HTML template through a scriptlet (<?= data ?>). The HTML template is then served as the response.
  • Client-Side: The HTML received by the client will already contain the data embedded within it, so no additional fetch requests are necessary.

This method integrates server-side script processing with client-side rendering, allowing you to seamlessly embed data into the HTML served to the user. It’s especially useful for applications that require initial data display without subsequent dynamic data loading.

TIP:

While working on the client side data, you can output your data object as a string value, then use it in your local IDE to update and finilize the code

Below will output a sheet into a textarea, select the contents and paste as the data in the local IDE

function doGet() {
  const template = HtmlService.createTemplateFromFile('Index');
  const sheet = SpreadsheetApp.openById(SHEETID).getSheets()[0];
  const data = sheet.getDataRange().getValues();
  template.data = JSON.stringify(data);
  return template.evaluate();
}

<!DOCTYPE html>
<html>
<head>
  <title>Spreadsheet Data</title>
  <script>
    const myData = <?= data ?>;
  </script>
</head>
<body>
  <h1>Spreadsheet Data</h1>
  <textarea id="output"></textarea>
  <script>
    document.querySelector('#output').value = myData;
  </script>
</body>
</html>

Cleint side code 

<!DOCTYPE html>
<html>
<head>
</head>
<body>
    <div id="output"></div>
    <script>
        const main = document.querySelector('#output');
        const myData = '[["test","test1"]]';
          const data = JSON.parse(myData);
        data.forEach(row => {
            const ele = document.createElement('div');
            ele.textContent = JSON.stringify(row);
            ele.style.borderBottom = '1px solid black';
            main.append(ele);
        });
    </script>
</body>
</html>