Search item as a "Tool Bar" in google sheet script

Question

I`m trying to add "Search Item" option in the tool bar. Basically, I have 6 different tabs in the same google sheet, but the # of items in each sheet is too many that users may not be able to find.

Is there way to add such tool in tool bar using Google sheet Script?

https://docs.google.com/spreadsheets/d/1lbfbMUhwKGG0CJ1tJuXVmwCRPAox_1dIATffhKVjkQM/edit?usp=sharing

Above is the link to the sample data

The data is generated from API for ad server system

In my real data, that "Creative" column has about > 1000 unique entries

Is there potentially, way to put option in a toolbar that does the "creative" search job, that can tell us creative "c6" was found in "first" tab, second row..

But search those items in only the "unhidden" tab

I`m not sure where I should start this with.. given the multiple tabs

Can someone please provide me suggestions?

Answer 1

• Input a search value.
  • You want to run Google Apps Script by the menu bar.
• Search the column "A" for each sheet using the search value, and when the searched value is found at the column "A", it retrieves the row.
  • There are sheets you don't want to use for searching.

• You want to show the result as follows.

  Creative "C6" was found in "Second", "fifth" tab
  Second Tab: Impression:100 / Click:100
  Fifth Tab: Impression:100 / Click:100
  

I understood like above. If my understanding is correct, how about this sample script? Please think of this as just one of them.

Flow:

1. When Spreadsheet is opened, the custom menu is added.
2. Run the script from "Show prompt" of the custom menu. By this, a dialog box is opened.
3. Input a search text to the text input field, and click "ok" button.
4. At the script, the rows are searched with TextFinder using the inputted search text.
5. Show the result by a dialog box as a sample.

Sample script:

Before you run the script, please set the sheet name to sheets.

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Custom Menu')
      .addItem('Show prompt', 'showPrompt')
      .addToUi();
}

function showPrompt() {
  // Please set the sheet name you want to use for searching.
  var sheets = ["first", "second", "third", "fourth", "fifth", "sixth"];

  var ui = SpreadsheetApp.getUi();
  var res = ui.prompt('Sample', 'Input search text:', ui.ButtonSet.OK_CANCEL);
  var button = res.getSelectedButton();
  if (button == ui.Button.OK) {
    var findText = res.getResponseText();
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var header = "Creative \"" + findText + "\" was found in ";
    var values = "";
    var object = sheets.reduce(function(obj, sheetName, i) {
      var sheet = ss.getSheetByName(sheetName);
      var textFinder = sheet.createTextFinder(findText);
      var searchedRows = textFinder.findAll().reduce(function(ar, e) {
        if (e.getColumn() == 1) {
          var row = sheet.getRange(e.getRow(), 1, 1, sheet.getLastColumn()).getValues()[0];
          if (row[0] === findText) {
            ar.push(row);
            header += '"' + sheetName + '"' + (i == sheets.length - 1 ? " tab\n" : ", ");
            values += sheetName + " Tab: Impression:" + row[1] + " / Click:" + row[2] + "\n";
          }
        }
        return ar;
      }, []);
      if (searchedRows.length > 0) obj[sheetName] = searchedRows;
      return obj;
    }, {});
    Logger.log(object);
    ui.alert(header + values);
  }
}

Input:

Output:

Note:

• This is a sample script. please modify this for your situation.

References:

• createTextFinder()
• findAll()
• Prompt dialogs
• reduce()

Edit:

• When the searched text was not found, you want to show "The creative you just searched for does not exist, please go back and check if there is any typo!".

In order to reflect your above request to the script, please modify above script as follows.

From:

ui.alert(header + values);

To:

if (Object.keys(object).length > 0) {
  ui.alert(header.slice(0, -2) + " tab\n" + values);
} else {
  ui.alert("The creative you just searched for does not exist, please go back and check if there is any typo!");
}