How to get a1Notations from Row with Findall function

user_16434184 0
Viewed 122

So I was referencing this question "https://stackoverflow.com/questions/56826834/how-to-get-a1notations-when-findall-function-returning-range-range-range-ran" and I like it and what it does but I need to go a little further and im still a beginner but this is what I have.

  var searchTerm = "Changeme"; // Please set this.
  var sheetName = "Sheet1"; // Please set this.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheetName = ss.getSheetByName(sheetName);
  var completeSearch = dataSheetName.createTextFinder(searchTerm).findAll();
  for (var i = 0; i < completeSearch.length; i++) {
    var range = completeSearch[i];
    var value = range.getValue();
    var rowValue = dataSheetName.getRange(range.getRow(), 1, 1, dataSheetName.getLastColumn()).getValues();
    Logger.log(value) // Value of the searched range
    Logger.log(rowValue) 
    Logger.log(range.getA1Notation()
    //Logger.log(rowValue) // Values of the row of searched range
  }
}

This is enough to get me the row that I am looking for and the Cell of what I am searching for but what I want to do is check a column for what I am searching for and return the row then grab the value of another column in the same row and based off that answer send an email out to someone with the answer.

So Search Column H for the "SearchTerm" If I found it in the row check the cell value in column A, B, and C and if then put the values in another sheet to send them out in an email.

Any help will be appreciated.

google-apps-script google-sheets full-text-search search
1 Answers

I believe your goal is as follows.

  • You want to retrieve the values of columns "A" to "C" by searching the value of searchTerm at the column "H".

In this case, how about the following modified script?

Modified script 1:

When you want to use TextFinder, how about the following script?

var searchTerm = "Changeme"; // Please set this.
var sheetName = "Sheet1"; // Please set this.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheetName = ss.getSheetByName(sheetName);
var completeSearch = dataSheetName.getRange("H:H").createTextFinder(searchTerm).findAll();
for (var i = 0; i < completeSearch.length; i++) {
  var range = completeSearch[i];
  var rowValue = dataSheetName.getRange(range.getRow(), 1, 1, 3).getValues();
  Logger.log(rowValue)
}

Modified script 2:

I thought that in your situation, at first, when the values are retrieved from the sheet and retrieve the expected values in a loop, the process cost might be a bit low. The sample script is as follows.

var searchTerm = "Changeme"; // Please set this.
var sheetName = "Sheet1"; // Please set this.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheetName = ss.getSheetByName(sheetName);
var values = dataSheetName.getRange("A1:H").getValues();
var res = values.reduce((ar, [a,b,c,,,,,h]) => {
  if (h == searchTerm) ar.push([a, b, c]);
  return ar;
}, []);
console.log(res);

References:

Edit:

From your sample Spreadsheet and your replying comments,

  • You want to search the column "H".
  • You want to use the values of columns "C,D,E" of the searched row.
  • About the values of recipient, subject, body, options of MailApp.sendEmail(recipient, subject, body, options), you will put subject and options as the constant value. recipient is from the column "G". body is like Here is the list of your employees that are in the sheet Last Name First Name Location 123 456 Test1 1r3 1t1 Test1.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  var searchTerm = "Supervisor1"; // Please set this.
  var sheetName = "Sheet1"; // Please set this.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheetName = ss.getSheetByName(sheetName);
  var values = dataSheetName.getRange("C1:H").getValues();
  var email = "";
  var res = values.reduce((ar, [c,d,e,,g,h]) => {
    if (h == searchTerm) {
      ar.push([c, d, e]);
      email = g;
    }
    return ar;
  }, []);
  if (res.length == 0 || !email) return;
  var message = {
    to: email,
    subject: "sample subject",
    body: "Here is the list of your employees that are in the sheet Last Name First Name Location\n\n" + res.map(r => r.join(",")).join("\n"), // Modified
    name: "Test",
    bcc: "testing",
    attachments: [SpreadsheetApp.getActiveSpreadsheet().getAs(MimeType.PDF).setName("Employee Report")]
  }
  MailApp.sendEmail(message);
}
user_71086530
Related