CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsfull-text-searchgoogle-sheets-api
Sharon Ferera
Sharon Ferera

Reputation: 1

Search partial string of a Cell in Google Sheet using Apps Script

I copied a scrip to search a Google Sheet but it will only return results if the search is the exact content of the cell. The cells have names. For example, row 1 have "John Doe". It will only bring results if I search for "John Doe". If I search just John or Doe or part of the name it will not return any resul.

    <div class="container">
        <br>
          <!-- ## SEARCH FORM ------------------------------------------------ -->
          <form id="search-form" class="form-inline" onsubmit="handleFormSubmit(this)">
                <div class="form-group mx-sm-3 mb-2">
                  <input type="text" class="form-control" id="searchtext" name="searchtext" placeholder="Digite parte do nome">
                </div>
                <button type="submit" class="btn btn-primary mb-2">Pesquisar</button>
              </form>
              <!-- ## SEARCH FORM ~ END ------------------------------------------- -->
        </div>
            <!-- ## TABLE OF SEARCH RESULTS ------------------------------------------------ -->
            <div id="search-results" class="table-responsive">
              <!-- The Data Table is inserted here by JavaScript -->
            </div>
            <!-- ## TABLE OF SEARCH RESULTS ~ END ------------------------------------------------ -->
          <br>
          </div>
        </div>
    </div>

Upvotes: 0

Views: 897

Answers (1)

Cooper
Cooper

Reputation: 64062

Try this:

Search for partial matches in spreadsheet

function search(needle="COL") {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  sh.clearContents();
  let a = ss.createTextFinder(needle).findAll().map(r => [r.getSheet().getName(),r.getA1Notation()]);
  sh.getRange("A1:C1").setValues([["Sheet Name","A1Notation",needle]])
  sh.getRange(2,1,a.length,2).setValues(a);
}

My Results:

Sheet Name A1Notation COL
Form Responses 7 B1
Form Responses 7 C1
Form Responses 6 B1
Form Responses 6 C1
Form Responses 6 D1
Form Responses 6 F1
Form Responses 6 G1
Form Responses 6 H1
Sheet0 C1
Sheet1 A1
Sheet1 B1
Sheet1 C1
Sheet1 D1
Sheet1 E1
Sheet1 F1
Sheet1 G1
Sheet1 H1
Sheet1 I1
Sheet1 J1
Sheet1 K1
Sheet1 L1
Sheet1 M1
Sheet1 N1
Sheet1 O1
Sheet1 P1
Sheet1 Q1
Sheet1 R1
Sheet1 S1
Sheet1 T1
Purchase C13
Current Podcasts A2
Current Podcasts F15
Current Podcasts F17
Current Podcasts E23
Current Podcasts F23
Current Podcasts F24
Current Podcasts B28
Current Podcasts E28
Current Podcasts F28
Current Podcasts F35
Current Podcasts A37
Current Podcasts F37
ProjectsFromSnippet D6
ProjectsFromSnippet D171
ProjectsFromSnippet B215
ProjectsFromSnippet D215
Listened Podcasts B160
Listened Podcasts A224
Listened Podcasts A240
Listened Podcasts A241
Listened Podcasts A242
Listened Podcasts A243
Listened Podcasts A249
Listened Podcasts B253
Listened Podcasts A256
Listened Podcasts A262
Listened Podcasts B267
Shared Drives A6
Shared Drives A619
Files E75
Files D111
Files E111
Files D229
Files E229
Files A377
Files D543
Files E543
Projects D6
Projects D213
Projects B265
Projects D265
Projects E568
Projects C630
Projects C642
Projects from Cloud Resource Manager API E81
Projects from Cloud Resource Manager API E119
Projects from Cloud Resource Manager API E149

Upvotes: 1

Related Questions