Skip to content
Advertisement

Row is always returning 1 – Google Scripts

I am trying to display an HTML page with values from the spreadsheet. I click on my Generate HTML page link and it redirects to the HTML page I created. It displays correctly except that the script always gets values from the first row. I am under the impression that the doGet() function does not accept rows? What could it be?

Code.gs

function fetchSpreadsheetValues(row) {
  var values = SpreadsheetApp.getActiveSheet().getRange(row, 1, row, 51).getValues(); // Get values from the specified range; original: row, 1, row, 51
  var rec = values[0];
  
  var candidate = {
    row: row, // always returning 1 (first row)
    r1: rec[0],
    r2: rec[1],
    r3: rec[2], 
    broker: rec[5], // Broker/Agent column
  };
  
  return candidate;
}
function doGet() { // must return HtmlOutput
  var cell = SpreadsheetApp.getActiveSheet().getCurrentCell(); // Get selected cell in the sheet
  var row = cell.getRow(); // Get selected cell's rows; always returning 1.
  
  var candidate = fetchSpreadsheetValues(row);
  
  var templ = HtmlService.createTemplateFromFile('agent-listing'); // Load HTML for the email; returns HtmlTemplate
  templ.candidate = candidate; // Assign candidate object to a variable in the template

  return templ.evaluate();
}

function onOpen() { // Add menu inside the spreadsheet
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Process').addItem('Send Email', 'showConfirmation').addItem('Generate HTML page', 'generateHTML').addToUi();
}

function generateHTML() {
  SpreadsheetApp.getUi()
  .showModalDialog(HtmlService.createHtmlOutputFromFile('openUrl').setHeight(50),"Generating HTML... Please wait.")
}

agent-listing.html

<!DOCTYPE html>
<html lang="en">
<head>
    <base target="_top">
    <meta charset="UTF-8">
    <title>Test</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <script>
      window.onload=function(){
        google.script.run.doGet();
      }
    </script>
</head>
<body>
  <div style="margin-left: 3%;">
    <!-- just to see the values -->
    <p>row <?= candidate.row ?>,</p>
    <p>R1 <?= candidate.r1 ?>,</p>
    <p>R2 <?= candidate.r2 ?>,</p>
    <p>R3 <?= candidate.r3 ?>,</p>
    <p>Hi <?= candidate.broker ?>,</p>
    <p>Test test</p>
  </div>
</body>
</html>

openUrl.html

<!DOCTYPE html>
<html>
  <head>
   <base target="_blank">
    <script>
     var url1 ='https://script.google.com/macros/s/AKfycbxU3VzpwPsBPEnfkPtMmCzo0YACc9UXPUlIcd6cm5Q/dev';
     var winRef = window.open(url1);
     winRef ? google.script.host.close() : window.alert('Allow popup to redirect you to '+url1) ;
     window.onload=function(){
       document.getElementById('url').href = url1;
     }
    </script>
  </head>
  <body>
    Kindly allow pop ups<br />or <a id='url'>click here</a> to continue!!!
  </body>
</html>

Advertisement

Answer

Here’s how I got it two work to pick up the current spreadsheet row and sheet. When I launch the dialog I use getScriptUrl() to get the current scriptApp.getService().getUrl() and to that I also add row and sheet name as query parameters to the url. I was planning on using them in the doGet() but in this version I decided to use cacheservice to store them for about 30 seconds which is more than enough time to launch ah3 which is like your agent listing and the dg() function uses them the get the active sheet and the current row.

I just tried it with doGet(e) and dg(e) using e.parameter.name and e.parameter.row to pass the row and sheet name so that the correct data could be displayed this also negates the need to call google.script.run.dg() in the ‘ah2.html’ file which is similar to your agent-listing.html.

My ‘ah3.html’ is similar to your ‘openURL.html’

<!DOCTYPE html>
<html>
<head>
    <base target="_blank">
    <script>
     window.onload=function(){
       google.script.run
       .withSuccessHandler(function(obj){
         var winRef = window.open(obj.url);
         winRef ? google.script.host.close() : window.alert('Allow popup to redirect you to '+ obj.url) ;
         document.getElementById('url').href = obj.url;
       })
       .getScriptUrl();
     }
    </script>
</head>

<body>
    Kindly allow pop ups<br />or <a id='url'>click here</a> to continue!!!
</body>

</html>

My ‘ah3.html’ is similar to your ‘agent-list.html’

<!DOCTYPE html>
<html lang="en">
<head>
    <base target="_top">
    <meta charset="UTF-8">
    <title>Test</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <script>
      window.onload=function(){
        google.script.run.dg();//remove this if you use the query string in doGet(e)
      }
    </script>
</head>
<body>
  <div style="margin-left: 3%;">
    <!-- just to see the values -->
    <p>row <?= candidate.row ?>,</p>
    <p>R1 <?= candidate.r1 ?>,</p>
    <p>R2 <?= candidate.r2 ?>,</p>
    <p>R3 <?= candidate.r3 ?>,</p>
    <p>Hi <?= candidate.broker ?>,</p>
    <p>Sheet Name <?= sheet ?>,</p>
    
    <p>Test test</p>
  </div>
</body>
</html>

My ag3.gs is similar to your google script code

function fetchSpreadsheetValues(row) {//this needs to be getting an obj with bow row and sheetname values  so if you decided to use cache service like I did then you'll need to modify this also.
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const values=sh.getRange(row, 1, sh.getLastRow()-row+1, 51).getValues(); 
  const rec=values[0];
  var candidate={row: row,r1:rec[0],r2:rec[1],r3:rec[2],broker:rec[5]};
  return candidate;
}
function doGet(e) {
  return dg(e);
}

function launchDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('ah3'),"Generating HTML... Please wait.");
}

function dg(e) {
  const ss=SpreadsheetApp.getActive();
  const cs=CacheService.getScriptCache();
  const sh=ss.getSheetByName(cs.get('name'));//use e.parameter.name here with querystring if you use them you can avoid the use of the cacheservice.
  var row=Number(cs.get('row'));//use e.parameter.row here with querystring
  var candidate=fetchSpreadsheetValues(row);
  var templ=HtmlService.createTemplateFromFile('ah2');
  templ.candidate=candidate;
  templ.sheet=sh.getName();
  return templ.evaluate();
}

function getScriptUrl() {
  const cs=CacheService.getScriptCache();
  const obj=getCurrentPosition();
  cs.put('row', obj.row, 30);
  cs.put('name', obj.name, 30);
  let robj={url:ScriptApp.getService().getUrl() + '?&row=' + obj.row + '&name=' + obj.name,row:obj.row,name:obj.name};
  Logger.log(JSON.stringify(robj));
  return robj;
}

function activesheettest() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  ss.toast(ss.getSheets()[0].getName());
}

function getCurrentPosition() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const cell=sh.getActiveRange();
  const row=cell.getRow();
  const robj={row:row,name:sh.getName()};
  Logger.log(JSON.stringify(robj));
  return robj;
}

This is the version that uses the webapp querystring with name and row append to the url.

ag3.gs:

function fetchSpreadsheetValues(obj) {
  const sheetname=obj.name;
  const row=obj.row;
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName(sheetname);
  const values=sh.getRange(row, 1, sh.getLastRow()-row+1, 51).getValues(); 
  const rec=values[0];
  var candidate={row: row,r1:rec[0],r2:rec[1],r3:rec[2],broker:rec[5]};
  return candidate;
}
function doGet(e) {
  return dg(e);
}

function launchDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('ah3'),"Generating HTML... Please wait.");
}

function dg(e) {
  const ss=SpreadsheetApp.getActive();
  const cs=CacheService.getScriptCache();
  const sh=ss.getSheetByName(e.parameter.name);
  var row=Number(e.parameter.row);
  var candidate=fetchSpreadsheetValues({row:e.parameter.row,name:e.parameter.name});
  var templ=HtmlService.createTemplateFromFile('ah2');
  templ.candidate=candidate;
  templ.sheet=sh.getName();
  return templ.evaluate();
}

function getScriptUrl() {
  const cs=CacheService.getScriptCache();
  const obj=getCurrentPosition();
  cs.put('row', obj.row, 30);
  cs.put('name', obj.name, 30);
  let robj={url:ScriptApp.getService().getUrl() + '?&row=' + obj.row + '&name=' + obj.name,row:obj.row,name:obj.name};
  Logger.log(JSON.stringify(robj));
  return robj;
}

function activesheettest() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  ss.toast(ss.getSheets()[0].getName());
}

function getCurrentPosition() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const cell=sh.getActiveRange();
  const row=cell.getRow();
  const robj={row:row,name:sh.getName()};
  Logger.log(JSON.stringify(robj));
  return robj;
}

ah2.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <base target="_top">
    <meta charset="UTF-8">
    <title>Test</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <script>
      window.onload=function(){
        //google.script.run.dg();
      }
    </script>
</head>
<body>
  <div style="margin-left: 3%;">
    <!-- just to see the values -->
    <p>row <?= candidate.row ?>,</p>
    <p>R1 <?= candidate.r1 ?>,</p>
    <p>R2 <?= candidate.r2 ?>,</p>
    <p>R3 <?= candidate.r3 ?>,</p>
    <p>Hi <?= candidate.broker ?>,</p>
    <p>Sheet Name <?= sheet ?>,</p>
    
    <p>Test test</p>
  </div>
</body>
</html>

ah3.html:

<!DOCTYPE html>
<html>
<head>
    <base target="_blank">
    <script>
     window.onload=function(){
       google.script.run
       .withSuccessHandler(function(obj){
         var winRef = window.open(obj.url);
         winRef ? google.script.host.close() : window.alert('Allow popup to redirect you to '+ obj.url) ;
         document.getElementById('url').href = obj.url;
       })
       .getScriptUrl();
     }
    </script>
</head>

<body>
    Kindly allow pop ups<br />or <a id='url'>click here</a> to continue!!!
</body>

</html>

And I just tested this version and it’s getting the data correctly now that I modified the fetchSpreadsheetValues() function. The version above is probably still incorrect because I didn’t modify that function up there.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement