I'm setting up a sidebar in Google Sheets to display information from rows to give my team a better view of certain data. Through a few of Mogsdad's answers (huge props) I was able to set up the sidebar HTML and display functions that update the DOM with info from whichever row is selected. I'm trying to expand this by running a custom search on a specific cell (string) within the row range and adding an element to the DOM that displays the first 5 Google search results; however, I'm having a tough time pointing to the string value in that specific cell (really a column in the array), running it in my custom search function, and getting the function that adds elements to the HTML to append the results.
The inspiration for this use case comes from Grant Timmerman's Apps Script demo at Angular Connect 2018, where he updates the sidebar with info about event speakers and pulls videos from Youtube using a search string from a specific cell. In the below code, you'll see that I pulled a lot from Mogsdad's sheet polling technique and their walkthrough on setting up a custom search engine using Google's API Key protocol.
Here's my .gs setup:
/**
*This function creates the sidebar in Sheets' UI based on HTML I set up *separately.
*/
function checkUpdates() {
var ui = HtmlService.createTemplateFromFile('CheckSidebar')
.evaluate()
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setTitle('Recent Sheet Updates');
SpreadsheetApp.getUi().showSidebar(ui);
};
/**
* Returns the active row.
* All based on Mogsdad's sheet polling function from 2015.
*/
function getRecord() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var headers = data[0];
var rowNum = sheet.getActiveCell().getRow();
if (rowNum > data.length) return [];
var record = [];
for (var col=0;col<headers.length;col++) {
var cellval = data[rowNum-1][col];
// Here I tried to set another variable to data[rowNum-1][2] to get some //specific data from column #3
Logger.log(cellval);
record.push({ heading: headers[col],cellval: cellval });
}
return record;
}
In a separate editor tab, I have the search function set up, also largely just following Mogsdad's instructions:
function SearchFetch(query) {
var urlTemplate = "https://www.googleapis.com/customsearch/v1?key=%KEY%&cx=%CX%&q=%Q%";
var ApiKey = "custom API Key";
var searchEngineID = "ID for the search engine";
var url = urlTemplate
.replace("%KEY%", encodeURIComponent(ApiKey))
.replace("%CX%", encodeURIComponent(searchEngineID))
.replace("%Q%", encodeURIComponent(query));
var params = {
muteExceptions: true
};
Logger.log(UrlFetchApp.getRequest(url, params));
var response = UrlFetchApp.fetch(url, params);
var respCode = response.getResponseCode();
if (respCode !== 200) {
throw new Error ("Error " +respCode+ " " + response.getContentText());
}
else {
var result = JSON.parse(response.getContentText());
Logger.log("Obtained %s search results in %s seconds.",
result.searchInformation.formattedTotalResults,
result.searchInformation.formattedSearchTime);
return result;
Finally, I have a function between tags in HTML that grabs the values from the array generated by getRecord() and dumps them into a DOM element with the class ID "floatypar" (for floaty-looking paragraphs, naturally). Lots of Mogsdad's markup in here:
function showRecord(record) {
if (record.length) {
for (var i = 0; i < record.length; i++) {
// build field name on the fly, formatted field-1234
var str = '' + i;
var fieldId = 'field-' + ('0000' + str).substring(str.length)
// If this field # doesn't already exist on the page, create it
if (!$('#'+fieldId).length) {
var newField = $($.parseHTML('<div id="'+fieldId+'"></div>'));
$('.floatypar').append(newField);
}
// Replace content of the field div with new record
$('#'+fieldId).replaceWith('<div id="'+fieldId+'" class="floatypar">' +record[i].cellval + '</div>');
}
}
//Setup the next poll
poll();
}
When I run this, the sidebar displays values from each cell in the row in their own div in the sidebar. However, I'm at a loss for passing the values from column #3 to the search engine and displaying results in their own sidebar div. Can anyone steer me in the right direction? Please do give props to Mogsdad in any case!