Google Sheets – Concatenation within IMPORTRANGE

Indeed, the first invocation of importrange cannot be nested within another function: the request for authorization does not "bubble up" (at first I thought this was an error on Google's part, but then considered that a single command can combine several importrange within it, so providing an authorization dialog in such a case would be extra complicated).

But you don't need a separate sheet to handle authorization. Just make the first call to the spreadsheet a single cell request: importRange("key","A1"). Then this can be replaced with the countunique that you want. Or, you can have a column with keys, and another column with a single cell request for authorization purpose.

Try this:

In your linked sample sheet, delete all of column D to prepare for the array formula.

Next, with the entire Column D selected, set the format: Format > Number > Time

Then place this formula in D1:

=ArrayFormula(IF(ROW(A:A)=1,"Shift start time",IF(A:A="","",IF(C:C="Clock-in","(start)",VLOOKUP(B:B&"Clock-in"&A:A-TIME(0,0,1),SORT({B:B&C:C&A:A,A:A},1,1),2,TRUE)))))

HOW IT WORKS

Of course, it's an array formula, since we wrapped it in ...

=ArrayFormula( )

First we check to see if we are in row 1. If so, we place the header there:

IF(ROW(A:A)=1,"Shift start time"

Then we check to see if the cell in Column A for the given row is blank. If so, the formula will also just leave a blank:

IF(A:A="",""

Next, if Column C contains "Clock-in" the formula will place "(start)":

`IF(C:C="Clock-in","(start)"

Nothing earth shattering thus far.

Finally, we want to create a virtual array in memory, and then search it with VLOOKUP.

The virtual search range will be "Frankensteined" together by concatenating existing pieces with one modification, and then sorting those concatenations:

SORT({B:B&C:C&A:A,A:A},1,1)

Curly brackets are another way to create an array. This virtual array will only have two columns:

1. The first column will be made up of single strings formed by joining NAME&"Check-in"&Timestamp. Each will wind up looking something like this in memory:

N43416.6152662037Clock-in

2. The second column of the virtual array will be just the Timestamp.

3. We sort this in ascending order using SORT on the first column. So in memory, those long strings we're forming will be in order by name and timestamp.

Now we're going to do a VLOOKUP on that sorted range. But we are going to create another concatenation to search for in each row: B:B&"Clock-in"&A:A-TIME(0,0,1)

Notice that everything is the same as the virtual range we formed except that we've subtracted 1 second from the Timestamp column.

Why?

Well, by sorting the virtual range, we can use TRUE as the last parameter of the VLOOKUP. This means that if VLOOKUP can't find an exact match, it will return the closest match that is less than our search. That's the key. By subtracting that one second, we force the VLOOKUP to find the closest match before our current time that also has a name match and says "Check-in."

Virtually, the VLOOKUP will get to the section of our Frankenstein range that has this person's name first. In that block of names, it will search for a listing with "Check-in" next and that has a time of exactly one second earlier than the "Check-out" time for each row. Of course, it won't find that, so it will back up and give you the last "Check-in" timestamp, because that would have been the last alpha-numeric listing that didn't go over the time-minus-one-second in that section.