{"id":17077,"date":"2025-01-08T21:32:39","date_gmt":"2025-01-08T12:32:39","guid":{"rendered":"https:\/\/84office.jp\/?p=17077"},"modified":"2025-01-08T21:32:40","modified_gmt":"2025-01-08T12:32:40","slug":"google-sheet-text-search","status":"publish","type":"post","link":"https:\/\/84office.jp\/en\/google-sheet-text-search\/","title":{"rendered":"How to Search Text Across All Sheets in a Google Spreadsheet Using Google Apps Script"},"content":{"rendered":"
<\/div>\n

You can search for text in a spreadsheet using shortcut keys like Ctrl + F<\/strong> or Ctrl + H<\/strong>, but when dealing with multiple sheets or large amounts of data, manual searching can be time-consuming.<\/p>\n\n\n\n

If you want to quickly identify which sheet and cell contain the relevant data, using Google Apps Script<\/strong> can significantly improve efficiency.<\/p>\n\n\n\n

In this article, we\u2019ll introduce a method to search text across multiple sheets at once using Google Apps Script.<\/p>\n\n\n\n

With this script, you can streamline your workflow and quickly access the information you need.<\/p>\n\n\n\n

Example Output<\/h2>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

Display a list of sheet names and cell locations containing the search keyword.<\/p>\n\n\n\n

Here\u2019s an example of a text search using the keyword “Tokyo”<\/strong>.<\/p>\n\n\n\n

The script searches for cells containing the keyword “Tokyo” across multiple sheets within a file and compiles the results into a list.<\/p>\n\n\n\n

    \n
  • Sheet Name<\/li>\n\n\n\n
  • Cell Location<\/li>\n\n\n\n
  • Cell Content<\/li>\n<\/ul>\n\n\n\n

    The search results are displayed on a newly created sheet, generated by Google Apps Script<\/strong>.<\/p>\n\n\n\n

    This script allows you to efficiently search and display keywords scattered across multiple sheets.<\/p>\n\n\n\n

    Additionally, by clicking on the cell location link, you can jump directly to the corresponding cell.<\/p>\n\n\n\n

    Steps<\/h2>\n\n\n\n
    \n
    STEP<\/span><\/div>
    Open Your Google sheets<\/strong><\/div>
    \n

    Start by opening the Google Sheets file you want to search.<\/p>\n\n\n\n

    To search across all sheets in the file, the position of the displayed sheet tabs doesn’t matter.<\/p>\n<\/div><\/div>\n\n\n\n

    STEP<\/span><\/div>
    Open Apps Script Editor<\/div>
    \n
    \"\"<\/figure>\n<\/div>\n\n\n

    Open a Google Spreadsheet and click on \u201cExtensions<\/strong>\u201d in the menu, then \u201cApps Script<\/strong>\u201d to open the Apps Script editor.<\/p>\n<\/div><\/div>\n\n\n\n

    STEP<\/span><\/div>
    Create the Script<\/strong><\/div>
    \n
    \"\"<\/figure>\n<\/div>\n\n\n

    Since the default function myFunction() {}<\/code> is present, delete it and replace it with the script below.<\/p>\n\n\n\n

    <\/path><\/path><\/svg><\/span>
    function<\/span> <\/span>searchAcrossSheets<\/span>() {<\/span><\/span>\n  <\/span>\/\/ Prompt the user to input the keyword to search for<\/span><\/span>\n  <\/span>var<\/span> keyword <\/span>=<\/span> Browser.<\/span>inputBox<\/span>(<\/span>"Enter the keyword to search for"<\/span>);<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Get the active spreadsheet<\/span><\/span>\n  <\/span>var<\/span> spreadsheet <\/span>=<\/span> SpreadsheetApp.<\/span>getActiveSpreadsheet<\/span>();<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Create a new sheet to store the search results<\/span><\/span>\n  <\/span>var<\/span> resultSheet <\/span>=<\/span> spreadsheet.<\/span>getSheetByName<\/span>(<\/span>"Search Results"<\/span>);<\/span><\/span>\n  <\/span>if<\/span> (resultSheet) {<\/span><\/span>\n    spreadsheet.<\/span>deleteSheet<\/span>(resultSheet);<\/span><\/span>\n  }<\/span><\/span>\n  resultSheet <\/span>=<\/span> spreadsheet.<\/span>insertSheet<\/span>(<\/span>"Search Results"<\/span>);<\/span><\/span>\n  resultSheet.<\/span>appendRow<\/span>([<\/span>"Sheet Name"<\/span>, <\/span>"Cell"<\/span>, <\/span>"Value"<\/span>]);<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Style the header row<\/span><\/span>\n  <\/span>var<\/span> headerRange <\/span>=<\/span> resultSheet.<\/span>getRange<\/span>(<\/span>1<\/span>, <\/span>1<\/span>, <\/span>1<\/span>, <\/span>3<\/span>);<\/span><\/span>\n  headerRange.<\/span>setHorizontalAlignment<\/span>(<\/span>"center"<\/span>).<\/span>setBackground<\/span>(<\/span>"#b6d7a8"<\/span>);<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Center-align the entire B column<\/span><\/span>\n  resultSheet.<\/span>getRange<\/span>(<\/span>1<\/span>, <\/span>2<\/span>, resultSheet.<\/span>getMaxRows<\/span>()).<\/span>setHorizontalAlignment<\/span>(<\/span>"center"<\/span>);<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Adjust column widths<\/span><\/span>\n  resultSheet.<\/span>setColumnWidth<\/span>(<\/span>1<\/span>, <\/span>150<\/span>); <\/span>\/\/ Sheet Name<\/span><\/span>\n  resultSheet.<\/span>setColumnWidth<\/span>(<\/span>2<\/span>, <\/span>100<\/span>); <\/span>\/\/ Cell<\/span><\/span>\n  resultSheet.<\/span>setColumnWidth<\/span>(<\/span>3<\/span>, <\/span>300<\/span>); <\/span>\/\/ Value<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Get all sheets in the spreadsheet<\/span><\/span>\n  <\/span>var<\/span> sheets <\/span>=<\/span> spreadsheet.<\/span>getSheets<\/span>();<\/span><\/span>\n  <\/span><\/span>\n  <\/span>\/\/ Loop through each sheet to search for the keyword<\/span><\/span>\n  sheets.<\/span>forEach<\/span>(<\/span>function<\/span>(<\/span>sheet<\/span>) {<\/span><\/span>\n    <\/span>if<\/span> (sheet.<\/span>getName<\/span>() <\/span>===<\/span> <\/span>"Search Results"<\/span>) <\/span>return<\/span>; <\/span>\/\/ Skip the results sheet<\/span><\/span>\n    <\/span><\/span>\n    <\/span>var<\/span> range <\/span>=<\/span> sheet.<\/span>getDataRange<\/span>();<\/span><\/span>\n    <\/span>var<\/span> values <\/span>=<\/span> range.<\/span>getValues<\/span>();<\/span><\/span>\n    <\/span><\/span>\n    <\/span>for<\/span> (<\/span>var<\/span> i <\/span>=<\/span> <\/span>0<\/span>; i <\/span><<\/span> values.length; i<\/span>++<\/span>) {<\/span><\/span>\n      <\/span>for<\/span> (<\/span>var<\/span> j <\/span>=<\/span> <\/span>0<\/span>; j <\/span><<\/span> values[i].length; j<\/span>++<\/span>) {<\/span><\/span>\n        <\/span>if<\/span> (values[i][j].<\/span>toString<\/span>().<\/span>indexOf<\/span>(keyword) <\/span>!==<\/span> <\/span>-<\/span>1<\/span>) {<\/span><\/span>\n          <\/span>\/\/ If the keyword is found, add the result to the results sheet<\/span><\/span>\n          <\/span>var<\/span> cell <\/span>=<\/span> range.<\/span>getCell<\/span>(i <\/span>+<\/span> <\/span>1<\/span>, j <\/span>+<\/span> <\/span>1<\/span>);<\/span><\/span>\n          <\/span>var<\/span> cellLink <\/span>=<\/span> <\/span>'=HYPERLINK("'<\/span> <\/span>+<\/span> spreadsheet.<\/span>getUrl<\/span>() <\/span>+<\/span> <\/span>'#gid='<\/span> <\/span>+<\/span> sheet.<\/span>getSheetId<\/span>() <\/span>+<\/span> <\/span>'&range='<\/span> <\/span>+<\/span> cell.<\/span>getA1Notation<\/span>() <\/span>+<\/span> <\/span>'", "'<\/span> <\/span>+<\/span> cell.<\/span>getA1Notation<\/span>() <\/span>+<\/span> <\/span>'")'<\/span>;<\/span><\/span>\n          resultSheet.<\/span>appendRow<\/span>([sheet.<\/span>getName<\/span>(), cellLink, values[i][j]]);<\/span><\/span>\n        }<\/span><\/span>\n      }<\/span><\/span>\n    }<\/span><\/span>\n  });<\/span><\/span>\n  <\/span><\/span>\n  Browser.<\/span>msgBox<\/span>(<\/span>"Search completed. Results are displayed in the 'Search Results' sheet."<\/span>);<\/span><\/span>\n}<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
    \n
    Script Description<\/strong><\/span><\/i><\/i><\/span><\/summary>
    \n
    \n
    Set the Keyword to Search<\/strong><\/strong><\/dt>\n\n\n\n
    \n
    <\/path><\/path><\/svg><\/span>
      <\/span>\/\/ Prompt the user to input the keyword to search for<\/span><\/span>\n  <\/span>var<\/span> keyword <\/span>=<\/span> Browser.<\/span>inputBox<\/span>(<\/span>"Enter the keyword to search for"<\/span>);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n