Currently, I'm working on a project to pull data from a Google Form/Spreadsheet and have it appear on a website using JQuery/DataTables. I had received help on getting the data to appear on the site, but I've hit a new issue.
Previous Question: Is it possible to create a public database (spreadsheet) search with Google Scripts?
On Google, I have a form which outputs eight columns:
- Timestamp
- Title
- Author
- Type
- URL
- Topic(s)
- Country(s)
- Tages
Of these, I don't need the timestamp to appear, and I would like the title to link to the URL if the URL exists. Since I'm not quite fluent with Javascript or DataTables, I made a second sheet which I tried to simplified it down to the following six:
- Title (currently being formatted as an
<a>tag using the URL) - Author
- Type
- Topic(s)
- Country(s)
- Tages
This almost works, except that the script I'm using to construct the table renders the Title field as it is in the cell with the <a href=""></a> being visible in the table. See the table under the title "Actual Output" for the current situation; the table under "Target Table Appearance" is what I'm aiming for.
Current Output: http://interculturalresources.weebly.com/webtest.html
Table Creation Script: http://www.weebly.com/uploads/1/7/5/3/17534471/tablescript.js
Thus, is there a way to have the <a href=""></a> be given as a link despite being put together in a Google Spreadsheet cell? Alternatively, is there a way to edit the current script to a) ignore the timestamp column and b) make the Title output a link to the URL from the URL column (with proper conditionals)?
EDIT: I'm focusing on the links now; I have a solution for the timestamp which involves copying the data to a new spreadsheet (since forms are strict with copy/pasting information). The current issue is getting each entry to have a link assuming the URL is in the first column, and the Title is in the second column. Please read Mogsdad's answer and my first comment for more information.
Solution: First, my thanks to Mogsdad for the "spark" of inspiration and insight which led me in the correct direction towards the solution. To explain the general idea, I wanted to not display one column (URL) from a Google Spreadsheet on the target website, yet use it's content to create a link in another (Title). Then, once the table was made, DataTables is used to format it. All cells in the table must contain something, so if the cell is to be blank, it must be filled with "None".
function cellEntries(json, dest, divId) {
var table = document.createElement('table');
table.setAttribute("id", divId + "table"); //Assign ID to <table> from the <div> name.
var thead = document.createElement('thead');
var tbody = document.createElement('tbody');
var thr;
var tr;
var entries = json.feed.entry;
var cols = json.feed.gs$colCount.$t; //The number of columns in the sheet.
var link; //Teporary holder for the URL of a row.
for (var i=0; i <cols; i++) { //For the first row of cells (column titles),
var entry = json.feed.entry[i];
if (entry.gs$cell.col == '1') { //For First Column / URL Column, (1)
if (thr != null) {
tbody.appendChild(thr);
}
thr = document.createElement('tr'); //Create <thr>/<tr> (???).
}
else { //For all other columns,
var th = document.createElement('th');
th.appendChild(document.createTextNode(entry.content.$t)); //Create title for each column.
thr.appendChild(th);
}
}
for (var i=cols; i < json.feed.entry.length; i++) { //For all remaining cells,
var entry = json.feed.entry[i];
if (entry.gs$cell.col == '1') { //For First Column / URL Column, (1)
if (tr != null) {
tbody.appendChild(tr);
}
tr = document.createElement('tr'); //Create <tr>.
hlink = entry.content.$t; //Put URL content into hlink.
}
else if (entry.gs$cell.col == '2') { //For Title Column,(2)
var td = document.createElement('td');
if (hlink != "None") { //If there is a link,
var alink = document.createElement('a'); //Make <a>
alink.appendChild(document.createTextNode(entry.content.$t)); //Put content in <a>
alink.setAttribute('href',hlink); //Assign URL to <a>.
td.appendChild(alink); //Put <a> in <td>.
}
else { //If there is no link,
td.appendChild(document.createTextNode(entry.content.$t)); //Put content in <td>.
}
tr.appendChild(td);
}
else { //For all other columns,
var td = document.createElement('td');
if (entry.content.$t != "None") { //If content is not "None",
td.appendChild(document.createTextNode(entry.content.$t)); //Output the content.
}
else { //Else,
td.appendChild(document.createTextNode("")); //Output a blank cell.
}
tr.appendChild(td);
}
}
$(thead).append(thr);
$(tbody).append(tr);
$(table).append(thead);
$(table).append(tbody);
$(dest).append(table);
$(dest + "table").dataTable();
};
function importGSS(json){
var divId = "targetdivid" //ID of the target <div>.
cellEntries(json, "#" + divId, divId);
};
