CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
MattH
MattH

Reputation: 3

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

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

Upvotes: 0

Views: 1152

Answers (2)

Wicket
Wicket

Reputation: 38131

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.

Related

Upvotes: 0

Tanaike
Tanaike

Reputation: 201378

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

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

    enter image description here

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.

enter image description here

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:

Upvotes: 1

Related Questions