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

CodePudding user response：

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

CodePudding user response：

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&"