Find cell address of each matching value in range - Google Apps Script

Question

I have a range that assigns shifts to a set of employees, in which the row labels are dates (ie, the Y axis is a chronological set of dates), and the column headers are locations (Building1, Building2, etc). Each row, then, contains employees assigned to each location for that day. Or, alternatively, each column will contain a chrono list of who will be assigned to the location specified in that column's header. I am attempting to match a name, say "John Doe" for each instance he appears throughout the range, and return a 2 column list of dates and locations for which he is assigned. John Doe will be listed many times over the dates in question and various locations (in multiple columns).
I've reached the limit of my expertise both with AppsScript and Filter functions and greatly appreciate any help. I believe a loop is necessary, but perhaps there is a better way. For what its worth, my goal is to take this list and put every assignment on the user's calendar (I've solved for this already). TIA everyone!

Sample input and output situation

Welcome to [so]. Please add a input data sample, the corresponding expected result, what you have tried and a brief description of your search efforts as is suggested in [ask]. — Rubén, Jul 21 '22 at 18:27
Thank you Rubén for taking the time to share the proper protocol on StackOverflow. An example of an input data sample and expected result is here: https://docs.google.com/spreadsheets/d/1lQNpKZUpw77vrETeyvEImsbzup6xyAjrK9N8QoQ--c8/edit?usp=sharing I have searched for solutions using AppsScript with variations of the title of my post, and for solutions using the Filter across multiple columns, with no success. I appreciate your guidance. — MattH, Jul 21 '22 at 21:10
When I saw your provided sample Spreadsheet, I have a question. In your sample Spreadsheet, there is a value of `John Doe` at "Building4" of "8/8/2022". But, in your expected sample result, it seems that this is not included. I cannot understand the logic of your goal. Can I ask you about the detailed logic for achieving your goal? — Tanaike, Jul 21 '22 at 22:43
You are welcome MathHH and thanks for the link. Please bear in mind that questions should be self contained, this means that all the relevant details should be included directly in the question body, i.e. besides including a link to demo spreadsheet, it is recommended to add some sample data (and in the case of Google Sheets, sample formulas too). — Rubén, Jul 21 '22 at 23:07
Thank you very much for the assistance and the guidance with SO protocols - very helpful indeed. — MattH, Jul 23 '22 at 11:33

score 1 · Accepted Answer · answered Jul 21 '22 at 23:11

From your provided Spreadsheet, I believe your goal is as follows.

You want to achieve the following situation using Google Apps Script.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. When you use this script, please put a custom function of =SAMPLE(Data!A3:F20,"John Doe") to a cell. By this, the result values are returned.

const SAMPLE = ([h, ...v], searchName) =>
  [["Data", "Location"], ...v.flatMap(([hh, ...vv]) => {
    const i = vv.indexOf(searchName);
    return i != -1 ? [[hh, h[i + 1]]] : [];
  })];

If you don't want to include the header row, you can also use the following script.

  const SAMPLE = ([h, ...v], searchName) =>
    v.flatMap(([hh, ...vv]) => {
      const i = vv.indexOf(searchName);
      return i != -1 ? [[hh, h[i + 1]]] : [];
    });

Testing:

When this sample script is used for your sample input values, the following situation is obtained.

In the case of "John Doe", from your expected output, "Building4" of "8/8/2022" is not included as shown in the red background cell. But, I'm worried that you might have miscopied. So, I proposed the above sample script. If you want to except for the value of the specific date, please tell me. This can be also achieved.

Reference:

Custom Functions in Google Sheets

score 0 · Answer 2 · answered Jul 21 '22 at 23:16

The result that you are looking for could be achieved by using Google Sheets built-in functions in a formula:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(Data!A4:A20&""&Data!B3:F3&""&Data!B4:F20),""),"SELECT Col1,Col2 WHERE Col3 = 'John Doe'")

Briefly, the above formula uses FLATTEN and Google Sheets array handling feature to "unpivot" your double entry table, then uses QUERY to filter and limit the data to be returned.

How do you create a "reverse pivot" in Google Sheets?

Thank you this taught me an alternative I was not aware of and incredibly useful - I appreciate your mentorship — MattH, Jul 23 '22 at 11:34

Find cell address of each matching value in range - Google Apps Script

2 Answers2

Sample script:

Testing:

Reference: