Here is the web app
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
Photoshopped output
Advertisement
Answer
I believe your goal as follows.
- You want to convert the images from
Sample DatatoPhotoshopped outputin 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.getis used. In this case, unfortunately, the hyperlinks cannot be directly retrieved. - In this case, I would like to propose the following flow.
- Retrieve values from the column “K” and retrieve the URLs with
getRichTextValues. - Create an array for returning to Javascript side.
- Retrieve values from the column “K” and retrieve the URLs with
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.