CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-api
codenewb
codenewb

Reputation: 75

Google Apps Script how to use page name to query google sheet

I have a simple Google Site setup that runs a Web App that queries a Google Sheet, and returns templated html code with the necessary queried information. I would like to add functionality that allows the Script to query the Sheet using the Page Name from where the Web App is embedded.

So, for example, say my website is: https://sites.google.com/view/SOMESITE

Say it has a few pages such as:

What I want to do is embed my web app in each of these sheets, and use the respective page name (i.e. Dave or Rick) as a parameter to refine the query the Web App does.

I have tried this, but the 'page' comes back Null. 

var page = SitesApp.getActivePage();
var name = Logger.log(page.getName());

How can I get the page name as a variable to use in my Script/Web App?

UPDATE:

I have revised my approach per the comments. So let's say I have the URL to my WebApp like this:

https://www.somedomain.com/blah/blah?Rick

And this is my Web App Code:

DoGet:

function doGet(e) {
  var name = e.queryString
  return HtmlService
      .createTemplateFromFile('index')
      .evaluate();
}

getData

function getData() {

     // ...here there is a bunch of code extracting data from my spreadsheet

      return [pick, title, artist, name]

    }

HTML:

<!DOCTYPE html>
<html>

  <head>
    <base target="_top">
  </head>

  <body>

    <? var data = getAlbum(); ?>
  // ... the rest is a bunch of markup that makes a table of the query results from getData

How to I pass the variable 'name', which should equal "Rick", to my getData function so I can use it when querying my Sheet?

Upvotes: 0

Views: 672

Answers (2)

Andres Duarte
Andres Duarte

Reputation: 3340

You can pass query parameters to your Web App URL, you can check here what query parameters are and how to use them. For example:

 https://www.somedomain.com/blah/blah?name=Rick

With the parameter attribute from the e event object, you can get the object with all the parameters you passed to the get request. You can use these values directly in your code.gs file, for example:

DoGet:

function doGet(e) {
  var parameters = e.parameter;
  getData(parameters);
  return HtmlService
      .createTemplateFromFile('index')
      .evaluate();
}

getData

function getData(parameters) {
 // ...here there is a bunch of code extracting data from my spreadsheet
  return [pick, title, artist, name]
}

Finally, if you need to communicate between the HTML file and the code.gs file, you should check out the documentation for it.

Upvotes: 1

Cooper
Cooper

Reputation: 64062

I didn't have enough room for this in the comments. But this is what the doGet() might look like:

function doGet(e) 
{
  //Logger.log('query params: ' + Utilities.jsonStringify(e));
  if(e.queryString !=='')
  {  
    switch(e.parameter.mode)
    {
      case 'page4':
        setPage('Page4')      
        return HtmlService
        .createTemplateFromFile('Page4')
        .evaluate()
        .addMetaTag('viewport', 'width=device-width, initial-scale=1')
        .setTitle("Page4"); 
        break;  
      case 'page3':
        setPage('Page3');        
        return HtmlService
        .createTemplateFromFile('Page3')
        .evaluate()
        .addMetaTag('viewport', 'width=device-width, initial-scale=1')
        .setTitle("Page3");
        break;
      case 'page2':
        setPage('Page2');        
        return HtmlService
        .createTemplateFromFile('Page2')
        .evaluate()
        .addMetaTag('viewport', 'width=device-width, initial-scale=1')
        .setTitle("Page2");
        break;  
      case 'page1':
         setPage('Page1');
         return HtmlService
        .createTemplateFromFile('Page1')
        .evaluate()
        .addMetaTag('viewport', 'width=device-width, initial-scale=1')
        .setTitle("Page1");
        break;
      default:
        setPage('Page1');
        return HtmlService
        .createTemplateFromFile('Page1')
        .evaluate()
        .addMetaTag('viewport', 'width=device-width, initial-scale=1')
        .setTitle("Page1");
        break;
    }
  }
  else
  {
    setPage('Page1');
    return HtmlService
    .createTemplateFromFile('Page1')
    .evaluate()
    .addMetaTag('viewport', 'width=device-width, initial-scale=1')
    .setTitle("Page1");
  }
}

Requirements for WebApps

Upvotes: 0

Related Questions