how to import data from one google sheet to another google sheet without appscript

Question

how to import data from one google sheet to another google sheet without appscript

hello

Thank you for your complete guidance.

I have two separate google sheets, the first sheet records data from the user side and each user has their own username and supervisor (Lid or Mah). The special tabs for each supervisor are the same in both sheets, but I want to get the data of the first sheet that is related to the data of the users and in the second sheet that is related to the conflict calculations. This is how every user enters their information in the first sheet and their information is either accepted or deleted. which is specified in column c. Now, in the second sheet which is related to the conflict, whenever I select a user in the dropdown, I want to check how many times that user has been repeated in the first sheet (Users Data). and record the number of repetitions in column b (Number of repetitions) of the second sheet. Then check how many times the selected user has "accepted" data in the first sheet and record the number in column c (Number of Accepted) of the second sheet. But I want these data to be dynamic for both Lid or Mah tabs, that is, whenever I change the username in the dropdown in Conflict sheet, it will receive and display all the information I said.

My two spreadsheets: Users Data Conflict

I also used the importrange method, but I encountered an error. i'm new to this.

Answer 1

Use query(), like this:

=let( 
  data, importrange($B1, B3 & "!" & $B2), 
  table, query( 
    data, 
    "select Col1, count(Col1) 
     where Col1 is not null 
     and Col3 matches 'Accept|Deleted' 
     group by Col1 
     pivot Col3 
     label Col1 '" & B3 & "' ", 
    0 
  ), 
  hstack( 
    table, 
    byrow(table, lambda(row, 
      if(sum(row), sum(row), "Total") 
    )) 
  ) 
)

To use the formula, place the parameters in cells B1:B3:

	A	B
1	spreadsheet	1ILnyV2_KEpIYuPiE-NCdhjl_TAApWoF5flhoOS8nw1E
2	range	A2:C
3	tab name	Lida1

The formula will give a table like this:

Lida1	Accept	Deleted	Total
user1	3	3	6
user2	3	4	7
user3	2	6	8
user4	4	4	8

The formula is based on my accepted answer at the Google Sheets forum.

See let(), query(), hstack(), byrow(), and the sample spreadsheet.