Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Created June 26, 2020 07:18
Show Gist options
  • Save tanaikech/64ab751e8fd8727d23e5f159c00e1579 to your computer and use it in GitHub Desktop.
Save tanaikech/64ab751e8fd8727d23e5f159c00e1579 to your computer and use it in GitHub Desktop.
Search Dialog Sample using TextFinder with Google Apps Script

Search Dialog Sample using TextFinder with Google Apps Script

This is a sample script for the search dialog using TextFinder with Google Apps Script. If this sample script could help to indicate the possibility of TextFinder, I'm glad.

Demo

In this demonstration, the value of test is searched. When "NEXT" is clicked, the next searched value is activated. When "PREVIOUS" is clicked, the previous searched value is activated. The search can be done for all sheets in the Google Spreadsheet.

Sample script

When you use this script, please copy and paste the following script to the container-bound script of Google Spreadsheet, and run the function of run(). By this, a dialog is opened to the Spreadsheet.

function searchText(searchValue, c) {
  const ranges = SpreadsheetApp.getActiveSpreadsheet()
    .createTextFinder(searchValue)
    .findAll();
  if (c < ranges.length) {
    ranges[c].activate();
    return c;
  }
  return --c;
}

function run() {
  const htmlStr = `
<input type="text" id="searchText" name="searchText">
<button id="previous" onclick="googleScript(c > 0 ? c - 1 : 0)">PREVIOUS</button>
<button id="next" onclick="googleScript(++c)">NEXT</button>
<script>
let c = -1;
const googleScript = (i) =>
  google.script.run.withSuccessHandler(cc => c = cc).searchText(document.getElementById("searchText").value, i);
</script>
`;
  const htmlObj = HtmlService.createHtmlOutput(htmlStr)
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
    .setWidth(350)
    .setHeight(50);
  SpreadsheetApp.getUi().showModelessDialog(htmlObj, "sample");
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment