Google App Script return each column two dimensional array with url

Question

I'm new to GAS and I'm trying to create an app that displays a searchable list of people, click on a unique id number and open another page to update data. I got the search to work using a different method but would only return data to the logs and not to the html table properly. I stumbled across this post last night which I thought would enabled me to create the link but I don't know how to display each column independently with the multi-dimensional array. The code below spits out the entire row, but I need the first column to be linked to a url that changes for each row depending on the data in the first column. I figured I would try to create a new column with the link calculated and if I grabbed that column instead it would work but no dice (this is why I'm pulling J instead of A). so row1 first column would be mylink.com?id=36, row2 would be mylink.com?id=400, etc.

So in summary I have 3 issues, if 2 & 3 are combined I'm ok with that.

filter/search multiple columns (with wildcards?) return results.
display multi-dimensional array each column independently (mainly to do #3).
grab the id in the first column and create a clickable url based upon the id in the first column.

code.gs

function getData()
{

  var targetRange = 'SearchList!A:J';
  var SQL = 'select J, B, C, D where J is not null';
  var Query = '=QUERY('+targetRange+',\"'+SQL+'\")';
  Logger.log(Query);

  var currentDoc = SpreadsheetApp.openByUrl(url);
  var tempSheet = currentDoc.insertSheet();

  var pushQuery = tempSheet.getRange(1, 1).setFormula(Query);
  var pullResult = tempSheet.getDataRange().getValues();
  Logger.log(pullResult);
  currentDoc.deleteSheet(tempSheet);  

  return pullResult;
}

html

<table class="table table-striped table-bordered table-hover">
          <thead>
            <td>Scripta ID</td>
            <td>Last Name</td>
            <td>First Name</td>
            <td>Birth Date</td>
          </thead>        
          <tbody id="table-body">            

<?     var data = getData(); 
       for (var i = 0; i < data.length; i++) 
       { 
?>
        <tr>
<?         for (var j = 0; j < data[i].length; j++) 
          { 
            Logger.log(data[i][j]);

?>
            <td><a href="<?= ScriptApp.getService().getUrl(); ?>?f=member&sid="&<?= data[i][j] ?>><?= data[i][j] ?></a></td>
<?        } 
?>
        </tr>
<?    } 
?>

        </tbody>
</table>

score 1 · Answer 1 · answered Apr 30 '20 at 01:54

To get column A only:

function getOne() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var vs=sh.getRange(1,1,sh.getLastRow(),1).getValues();
  vs.forEach(function(r,i){
    //r[0] is columnA for each row
  });
  //or
  for(var i=0;i<vs.length;i++) {
    //vs[i][0] is column A for each row
  }
}

Sheet.getRange(row, col, numrows, numcols)

Google App Script return each column two dimensional array with url

1 Answers1