I have a Google sheets where i’m using a simple query function to import data from another Google sheets (SPREADSHEET 01)
SPREADSHEET 01 contains:
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| NAME 01 | PRODUCT 01 | CITY 01 | ✓ |
| NAME 02 | PRODUCT 02 | CITY 02 | X |
| NAME 03 | PRODUCT 03 | CITY 03 | ✓ |
SPREADSHEET 02
I’M DOING THE FOLLOWING:
=QUERY(IMPORTRANGE(“SPREADSHEET 01 URL”,”Sheet1!A1:E”),”SELECT \* WHERE Col4=’✓'”)
THE RESULT:
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| NAME 01 | PRODUCT 01 | CITY 01 | ✓ |
| NAME 03 | PRODUCT 03 | CITY 03 | ✓ |
LET’S SAY I’M DOING A CHECK TO EACH RESULT IN AN NEW COLUMN (column 05)
| Column A | Column B | Column C | Column D | Column E |
|---|---|---|---|---|
| NAME 01 | PRODUCT 01 | CITY 01 | ✓ | Done |
| NAME 03 | PRODUCT 03 | CITY 03 | ✓ | Done |
NOW IF THE ROW 2 IN SPREADSHEET 01 TURNS THE “X” TO “✓” WILL RUINS MY CHECKING PROGRESS AND BECOME LIKE THIS:
| Column A | Column B | Column C | Column D | Column E |
|---|---|---|---|---|
| NAME 01 | PRODUCT 01 | CITY 01 | ✓ | Done |
| NAME 02 | PRODUCT 02 | CITY 02 | ✓ | Done |
| NAME 03 | PRODUCT 03 | CITY 03 | ✓ |
*** AS YOU CAN SEE THE 2nd ROW WHERE column 5 CONTAINS “DONE” IT’S ACTUALLY RETURNS TO THE 3rd ROW
MY QUESTION IS: IS THERE A FUNCTION WHERE IT CAN FIX THIS ISSUE? AND SHOWS THE RESULTS LIKE THIS:
| Column A | Column B | Column C | Column D | Column E |
|---|---|---|---|---|
| NAME 01 | PRODUCT 01 | CITY 01 | ✓ | Done |
| NAME 02 | PRODUCT 02 | CITY 02 | ✓ | |
| NAME 03 | PRODUCT 03 | CITY 03 | ✓ | Done |
Any help would be very much appreciated. Thank you!
I TRIED MANY FORMULAS AND FUNCTIONS YET I HAVEN'T FOUND ANY SOLUTION!