Issue:
FilterView could not be set from apps script
Workaround:
In this workaround, The main con is that, You'd need to open the Google sheets through a url and that url tab/window will run in the background. You can also easily do this with plain =HYPERLINK() formulas using the same logic without any apps script.
- FilterViews has the following url hash appended to sheets edit url:
gid=SHEET_ID&fvid=FILTERVIEW_ID
- Publish a web app(
HookService) for the explicit purpose of opening Google sheets url.
- Using the web-app, It is possible to change Google sheets url.
- Sidebar can be opened in Google sheets with filterview buttons
- Sidebar will then connect to the web-app(through
window.postMessage) and command it to change Google sheets url. This is needed because sidebar cannot directly change the Google sheets url, but a web-app can.
Sample script:
WebApp: hookService.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<title></title>
</head>
<body>
<h1><span>Starting</span> HookService on Google sheets....</h1>
<div id="main">
<p>This is needed to set filter views</p>
<p>Kindly allow pop ups to continue....</p>
<label
>Your Google sheets ID:<input
id="sheets"
type="text"
value="<?=id?>"
autocomplete/></label
><br />
<label>Sidebar Origin:<input id="origin" type="url" autocomplete/></label
><br />
<button>START HOOK</button>
</div>
<script>
var win1, win1url;
let [div, input, origin, button] = [
...document.querySelectorAll('div,input,button'),
];
origin.value = window.location.origin;
function openSheets() {
win1 = window.open(
(win1url =
'https://docs.google.com/spreadsheets/d/' + input.value + '/edit'),
'_blank'
);
if (win1) {
document.querySelector('span').textContent = 'Running';
window.addEventListener('message', receiveMessage, false);
div.innerHTML = 'Close this window to stop Hook Service';
} else {
alert('Pop up blockers must be disabled on this site to run hook');
}
}
openSheets();
button.addEventListener('click', openSheets);
function receiveMessage(event) {
//[SECURITY] Check event origin
if (event.origin !== origin.value) {
console.error('origin disallowed');
alert('Origin not allowed');
return;
}
win1.location.replace(win1url + event.data);
}
</script>
</body>
</html>
SideBar: hookSubService.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<title></title>
<style>
div {
display: flex;
flex-direction: column;
}
</style>
</head>
<body>
<div>
<button id="1">FILTER VIEW 1</button>
<button id="2">FILTER VIEW 2</button>
</div>
<script>
const win0 = window.top.opener;
if (!win0) {
alert('Hook service not found! Exiting...');
google.script.host.close();
}
/************TODO: Set sheet ids and filterview ids************/
const gids = ['Sheetid for filter view1', 'Sheetid for filterview2'];
const fvids = ['filterviewid1', 'filterviewid2'];
let div = document.querySelector('div');
div.addEventListener('click', e => {
let id = Number(e.target.id) - 1;
let [gid, fvid] = [gids[id], fvids[id]];
let message = '#gid=' + gid + '&fvid=' + fvid;
//TODO:[SECURTY] Provide webapp origin url instead
win0.postMessage(message, window.location.origin);
});
</script>
</body>
</html>
Server side code:code.gs
function doGet(e){
var page=e.parameter.page
var out = HtmlService.createTemplateFromFile(page || 'hookService');
out.id = SpreadsheetApp.getActive().getId();
return out.evaluate().addMetaTag('viewport', 'width=device-width, initial-scale=1');
}
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('Sidebar').addItem('Show FilterView', 'showSidebar').addToUi();
}
function showSidebar() {
SpreadsheetApp.getUi().showSidebar(
HtmlService.createHtmlOutputFromFile('hookSubService.html')
)
}
- Set sheet Ids and filterview ids above in HookSubService.html
- Publish the web-app from script editor
- Open the web app from the published link
- Web app must be bound to the Spreadsheet's script editor
References:
