Skip to content
Advertisement

GAS, Bootstrap Web form Searches and Shows Data in a table, BUT it does NOT show links or hyperlinks to click on them (from spreadsheet)

Here is the web app

https://script.google.com/macros/s/AKfycbyEHj5qtIeCZh4rR6FutBLQ3N9NihreaTv7BFj4_saOfNWJUG0Tn2OtvzQs4zASYHnNiA/exec

Any idea on how could I make it show up the links or hyperlinks some cells have?

I have made this web form mainly following a tutorial so I’m somewhat new, I try to understand most of the code but truly it’s quite hard for me so if this is something that’s too hard for a noob to handle I understand

And here is the code code.gs

      function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate();
}


/* PROCESS FORM */
function processForm(formObject){  
  var result = "";
  if(formObject.searchtext){//Execute if form passes search text
      result = search(formObject.searchtext);
  }
  return result;
}

//SEARCH FOR MATCHED CONTENTS 
function search(searchtext){
  var spreadsheetId   = '1xsSrUT8jYm9dT_Mfi2UTy4BHjcD7TQVVtgsB1x1wgTE'; //** CHANGE !!!
  var dataRage        = 'Data!A2:Y';                                    //** CHANGE !!!
  var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRage).values;
  var ar = [];
  
  data.forEach(function(f) {
    if (~f.indexOf(searchtext)) {
      ar.push(f);
    }
  });
  return ar;
}

And the 2nd file

Index HTML file

<!DOCTYPE html>
<html>
    <head>
        <base target="_top">
        <link href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
        <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
        <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.bundle.min.js" integrity="sha384-xrRywqdh3PHs8keKZN+8zzc5TX0GRTLCcmivcbNJWm2rs5C8PRhcEn3czEjhAO9o" crossorigin="anonymous"></script>
        
        <!--##JAVASCRIPT FUNCTIONS ---------------------------------------------------- -->
        <script>
          //PREVENT FORMS FROM SUBMITTING / PREVENT DEFAULT BEHAVIOUR
          function preventFormSubmit() {
            var forms = document.querySelectorAll('form');
            for (var i = 0; i < forms.length; i++) {
              forms[i].addEventListener('submit', function(event) {
              event.preventDefault();
              });
            }
          }
          window.addEventListener("load", preventFormSubmit, true); 
             
          
          //HANDLE FORM SUBMISSION
          function handleFormSubmit(formObject) {
            google.script.run.withSuccessHandler(createTable).processForm(formObject);
            document.getElementById("search-form").reset();
          }
        
          //CREATE THE DATA TABLE
          function createTable(dataArray) {
            if(dataArray && dataArray !== undefined && dataArray.length != 0){
              var result = "<table class='table table-sm table-striped' id='dtable' style='font-size:0.8em'>"+
                           "<thead style='white-space: nowrap'>"+
                             "<tr>"+                               //Change table headings to match witht he Google Sheet
                              "<th scope='col'>ORDERNUMBER</th>"+
                              "<th scope='col'>QUANTITYORDERED</th>"+
                              "<th scope='col'>PRICEEACH</th>"+
                              "<th scope='col'>ORDERLINENUMBER</th>"+
                              "<th scope='col'>SALES</th>"+
                              "<th scope='col'>ORDERDATE</th>"+
                              "<th scope='col'>STATUS</th>"+
                              "<th scope='col'>QTR_ID</th>"+
                              "<th scope='col'>MONTH_ID</th>"+
                              "<th scope='col'>YEAR_ID</th>"+
                              "<th scope='col'>PRODUCTLINE</th>"+
                              "<th scope='col'>MSRP</th>"+
                              "<th scope='col'>PRODUCTCODE</th>"+
                              "<th scope='col'>CUSTOMERNAME</th>"+
                              "<th scope='col'>PHONE</th>"+
                              "<th scope='col'>ADDRESSLINE1</th>"+
                              "<th scope='col'>ADDRESSLINE2</th>"+
                              "<th scope='col'>CITY</th>"+
                              "<th scope='col'>STATE</th>"+
                              "<th scope='col'>POSTALCODE</th>"+
                              "<th scope='col'>COUNTRY</th>"+
                              "<th scope='col'>TERRITORY</th>"+
                              "<th scope='col'>CONTACTLASTNAME</th>"+
                              "<th scope='col'>CONTACTFIRSTNAME</th>"+
                              "<th scope='col'>DEALSIZE</th>"+
                            "</tr>"+
                          "</thead>";
              for(var i=0; i<dataArray.length; i++) {
                  result += "<tr>";
                  for(var j=0; j<dataArray[i].length; j++){
                      result += "<td>"+dataArray[i][j]+"</td>";
                  }
                  result += "</tr>";
              }
              result += "</table>";
              var div = document.getElementById('search-results');
              div.innerHTML = result;
            }else{
              var div = document.getElementById('search-results');
              //div.empty()
              div.innerHTML = "Data not found!";
            }
          }
        </script>
        <!--##JAVASCRIPT FUNCTIONS ~ END ---------------------------------------------------- -->
        
    </head>
    <body>
        <div class="container">
            <br>
            <div class="row">
              <div class="col">
            
                  <!-- ## SEARCH FORM ------------------------------------------------ -->
                  <form id="search-form" class="form-inline" onsubmit="handleFormSubmit(this)">
                    <div class="form-group mb-2">
                      <label for="searchtext">Search Text</label>
                    </div>
                    <div class="form-group mx-sm-3 mb-2">
                      <input type="text" class="form-control" id="searchtext" name="searchtext" placeholder="Search Text">
                    </div>
                    <button type="submit" class="btn btn-primary mb-2">Search</button>
                  </form>
                  <!-- ## SEARCH FORM ~ END ------------------------------------------- -->
              
              </div>    
            </div>
            <div class="row">
              <div class="col">
            
                <!-- ## TABLE OF SEARCH RESULTS ------------------------------------------------ -->
                <div id="search-results" class="table-responsive">
                  <!-- The Data Table is inserted here by JavaScript -->
                </div>
                <!-- ## TABLE OF SEARCH RESULTS ~ END ------------------------------------------------ -->
                  
              </div>
            </div>
        </div>
    </body>
</html>

Sample Data sample data used here

Photoshopped output

Desired Output

Advertisement

Answer

I believe your goal as follows.

  • You want to convert the images from Sample Data to Photoshopped output in your question.
  • The column “K” has the hyperlinks and you want to set the hyperlinks to the HTML side.

Modification points:

  • When I saw your script, Sheets.Spreadsheets.Values.get is used. In this case, unfortunately, the hyperlinks cannot be directly retrieved.
  • In this case, I would like to propose the following flow.
    1. Retrieve values from the column “K” and retrieve the URLs with getRichTextValues.
    2. Create an array for returning to Javascript side.

When above points are reflected to your script, it becomes as follows.

Modified script:

Please modify the function search in Google Apps Script side as follows.

function search(searchtext) {
  var spreadsheetId   = '1xsSrUT8jYm9dT_Mfi2UTy4BHjcD7TQVVtgsB1x1wgTE'; //** CHANGE !!!
  var dataRage        = 'Data!A2:Y';                                    //** CHANGE !!!

  // 1. Retrieve values from the column "K" and retrieve the URLs with getRichTextValues.
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var [sheetName, a1Notation] = dataRage.split("!");
  var sheet = ss.getSheetByName(sheetName);
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(a1Notation + lastRow);
  var richTextValues = range.offset(0, 10, lastRow, 1).getRichTextValues().map(([k]) => {
    var url = k.getLinkUrl();
    var text = k.getText();
    return url ? `<a href="${url}">${text}</a>` : text;
  });

  // 2. Create an array for returning to Javascript side.
  var values = range.getDisplayValues().reduce((ar, r, i) => {
    if (r.includes(searchtext)) {
      r[10] = richTextValues[i];
      ar.push(r);
    }
    return ar;
  }, []);
  return values;
}

Note:

  • When you modified the Google Apps Script, please modify the deployment as new version. By this, the modified script is reflected to Web Apps. Please be careful this.
  • You can see the detail of this at the report of “Redeploying Web Apps without Changing URL of Web Apps for new IDE“.
  • I proposed above modified script using your sample input and output images. So, when your actual situation is different from it, the script might not be able to be used. Please be careful this.

References:

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