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>