RegEx Search Using TextFinder
Code.gs
You will need to create a sheet called "Globals". In cell "A1" put 'ssid'. Don't include the quotes. Below is an image of a dialog I just made this morning.
function onOpen(){
SpreadsheetApp.getUi().createMenu('My Menu')
.addItem("Show Dialog", 'showMyDialog')
.addToUi()
}
function showMyDialog(){
var ui=HtmlService.createTemplateFromFile('search').evaluate().setHeight(450).setWidth(600);
SpreadsheetApp.getUi().showModelessDialog(ui, 'Regular Expression Spreadsheet Search');
}
function doGet(e){
return HtmlService.createTemplateFromFile('search').evaluate();
}
function include(filename){
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function getScriptURL(){
return ScriptApp.getService().getUrl();
}
function getSelectOptions() {
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Options');
var rg=sh.getDataRange();
var vA=rg.getValues();
return vA;
}
function regexSearch(sObj) {
var ss=SpreadsheetApp.openById(sObj.ssid);
var tf=ss.createTextFinder(sObj.pattern).useRegularExpression(true);
var all=tf.findAll();
var matches='';
for(var i=0;i<all.length;i++) {
matches+=Utilities.formatString('Sheet: %s Cell: %s Value: %s\n',all[i].getSheet().getName(),all[i].getA1Notation(),all[i].getValue());
}
if(matches) {
sObj.matches=matches;
}else{
sObj.message(Utilities.formatString('No Matches found for %s',sObj.pattern));
}
return sObj;
}
function saveSsId(sObj) {
if(sObj.ssid) {
setGlobal('ssid',sObj.ssid);
var ss=SpreadsheetApp.openById(sObj.ssid);
var name=ss.getName();
sObj['name']=name;
sObj.message='';
}else{
sObj.message='Invalid Input: Spreadsheet Id not received. At saveSsId() in Code';
}
return sObj;
}
function getLastSsId() {
var ssid=getGlobal('ssid');
var rObj={ssid:ssid,name:'',message:''};
if(rObj.ssid) {
var ss=SpreadsheetApp.openById(ssid);
if(ss) {
var name=ss.getName();
rObj.name=name;
}
}
return rObj;
}
globals.gs
function getGlobals(){
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Globals');
var rg=sh.getRange(1,1,sh.getLastRow(),2);
var vA=rg.getValues();
var g={};
for(var i=0;i<vA.length;i++){
g[vA[i][0]]=vA[i][1];
}
return g;
}
function setGlobals(dfltObj){
var dfltH=Object.keys(dfltObj).length;
if(dfltObj){
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Globals');
var rg=sh.getRange(1,1,dfltH,2);
var vA=rg.getValues();
for(var i=0;i<dfltH;i++){
vA[i][1]=dfltObj[vA[i][0]];
}
rg.setValues(vA);
}
}
function getGlobal(name){
return getGlobals()[name];
}
function setGlobal(name,value){
var curObj=getGlobals();
if(!curObj.hasOwnProperty(name)) {
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Globals');
sh.appendRow([name,value])
}else{
curObj[name]=value;
setGlobals(curObj);
}
}
function cleanGlobals() {
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Globals');
if(sh.getLastColumn()>2) {
sh.getRange(1,3,sh.getLastRow(),sh.getLastColumn()-2).clearContent();
}
var rg=sh.getRange(1,1,sh.getLastRow(),2);
var vA=rg.getValues();
for(var i=0;i<vA.length;i++) {
if(!vA[i][0] || !vA[i][1]) {
var userInterface=HtmlService.createHtmlOutput('Globals Sheet Requires Maintenance...Do it know.' + ' Check Row ' + Number(i + 1));
SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Global Maintenance Required');
break;
}
}
}
form.html
<h1>RegEx Search Using TextFinder</h1>
<label for="txt1">RegEx</label><br /><textarea id="txt1" name="RegEx" cols="60" rows="2"></textarea>
<br /><label for="txt2">Matches</label><br /><textarea id="txt2" name="Matches" cols="60" rows="4" ></textarea>
<br /><input id="btn1" type="button" value="Search" onClick="search();" />
<br /><input type="text" id="txt3" size="40" /><input type="button" value="Save SsId" onClick="saveSsId();" />
<br /><input type="text" id="txt4" size="40" placeholder="readOnly" readOnly/><label for="txt4">Spreadsheet Name</label>
<div id="msg1"></div>
<br /><input type="button" value="Close" onClick="Exit();" />
script.html
<script>
$(function(){
/*
google.script.run
.withSuccessHandler(function(vA) {
$('#sel1').css('background-color','#ffffff');
updateSelect(vA);
})
.getSelectOptions();
*/
google.script.run
.withSuccessHandler(function(rObj){
if(rObj.ssid && rObj.name) {
$('#txt3').val(rObj.ssid);
$('#txt4').val(rObj.name);
$('#msg1').val('Last Spreadsheet recovered.');
}else{
$('#txt3').val('');
$('#txt4').val('');
$('#msg1').val('Last Spreadsheet not recovered.');
}
})
.getLastSsId();
});
function search() {
var pattern=$('#txt1').val();
var ssid=$('#txt3').val();
if(pattern && ssid) {
$('#txt1').css('background-color','#ffff00');
var sObj={pattern:pattern,ssid:ssid};
google.script.run
.withSuccessHandler(function(rObj){
$('#txt1').css('background-color','#ffffff');
$('#txt2').val(rObj.matches);
$('#msg1').val(rObj.message);
})
.regexSearch(sObj);
}else{
$('#msg1').val("Invalid Input: missing or invalid RegEx Pattern (Please do not use //) or no Spreadsheet Id");
}
}
function saveSsId() {
var ssid=$('#txt3').val();
if(ssid) {
$('#txt3').css('background-color','#ffff00');
var sObj={ssid:ssid};
google.script.run
.withSuccessHandler(function(rObj){
$('#txt3').css('background-color','#ffffff');
$('#txt4').val(rObj.name);
$('#msg1').val(rObj.message);
})
.saveSsId(sObj);
}else{
$('msg1').val('Invalid Input: missing Spreadsheet Id');
}
}
function updateSelect(vA,id){
var id=id || 'sel1';
var select = document.getElementById(id);
select.options.length = 0;
for(var i=0;i<vA.length;i++)
{
select.options[i] = new Option(vA[i][0],vA[i][1]);
}
}
function getInputObject(obj) {
var rObj={};
for(var i=0;i<Object.keys(obj).length;i++){
//console.log('Name: %s Type: %s',obj[i].name,obj[i].type);
if(obj[i].type=="text"){
rObj[obj[i].name]=obj[i].value;
}
if(obj[i].type=="select-one"){
rObj[obj[i].name]=obj[i].options[obj[i].selectedIndex].value;
}
}
return rObj;
}
function Exit() {
console.log('close');
google.script.host.close();//as dailog
//google.script.run.withSuccessHandler(function(url){window.open(url,'_top');}).getScriptURL();//as a webapp
//https://stackoverflow.com/a/47754851/7215091
}
console.log('My Code');
</script>
css.html
<style>
body {background-color:#ffffff;}
select,input[type="button"],input[type="text"]{margin:2px 2px;}
</style>
resources.html
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
search.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<?!= include('resources') ?>
<?!= include('css') ?>
</head>
<body>
<?!= include('form') ?>
<?!= include('script') ?>
</body>
</html>