How can I display in my google site a count of rows in my google sheet having a given value in the given column?

Kenneth Whitmore 0
Viewed 22

The goal: In a google sites page, display the count of rows in a specific google sheet that have the given value in the given column, without displaying the full sheet in the google sites page.

The problem: I haven't found any server-side support for creating functions that pull data from a google sheet and can be called from the client-side javascript.

What I've tried: I've looked at creating google apps in sheets, but they don't appear to be callable from a javascript embedded in a google sites page.

Example: Google sites page 1: Volunteer Sign-up Form Google sheet: Volunteer Sign-up Form (responses) Columns: Timestamp, E-Mail, First-Name, Last-Name, Phone, Interests Google sites page 2: Available Follow-up Tasks Display: Interview Requests ()

Step 1: Publish Google sites page 1 and allow site visitors to fill in and submit their information, which is automatically added to the Google sheet.

Step 2: Create a server-side script to count all rows in Volunteer Sign-up Form (responses), where the "interests" value matches "Request an interview". (Do I do this in app-script, or what is the best way?)

Step 3: Embed a javascript into Google Sites page 2 to call a function in the server-side script to retrieve the matching row count.

I've worked with MVC in C# together with javascript/jquery and JSON in the client-side before, as well as PHP on the server-side, so I'm familiar with the client-server stack concept, but just am not yet familiar with Google's implementation.

Are there any good resources that can enlighten me on this, or can someone show me a working example?

google-sites google-sheets google-apps-script javascript
1 Answers

Embedding a Webapp in new Sites

GS:

function doGet(e) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  return HtmlService.createHtmlOutput(`<h1>Number of Lines: ${sh.getLastRow()} </h1>`);
}

Follow the procedure found here

enter image description here

Here is a version with a little more meat to it:

GS:

function doGet(e) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  let t = HtmlService.createTemplateFromFile("ah1");
  t.lr = sh.getLastRow();
  return t.evaluate();
}
function getMyData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  return sh.getDataRange().getValues();
}

HTML:

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <div id="tabledata">
       <? var vs = getMyData(); ?>
       <table>
         <? vs.forEach((r,i)=>{ ?>
           <tr>
           <? r.forEach((c,j)=>{ ?>
             <? if(i == 0) { ?>
            <th style="padding:2px 5px;font-weight:bold;border:1px solid black;"><?= c ?> </th>           
           <? } else { ?>
             <td style="padding:2px 5px;border:1px solid black;"><?= vs[i][j] ?> </td>
           <? } ?>
         <?  }); ?>
           </tr>
         <? }); ?>
       </table>
     </div>
     <h3> Number of rows: <?= lr ?> </h3>
</body>
</html>

Output on Sites:

enter image description here

user30hu0
Related