0

https://docs.google.com/spreadsheets/d/1XFbSKOgwFlsRahaOOCq1-EZPR4JcmaT2ywUc2RQffnk/edit?usp=drivesdk

I use this sheet to list data of patients in my hospital. Also in the last tab (Possible_Discharge), I query all the patients that their value of column I is TRUE in the tab (All_Patients) using this query function: =query(All_Patients!B5:J,"select B,C,D,E,F,G,H where B is not null AND I =TRUE order by B",0)

I also embedded code to clear contents of the sheet whenever I want to fill new data.

The problem I face is every time I fill new data after clearing the contents, The function of query (TRUE) results doesn't work. So, every time I change the TRUE to 'TRUE' or "TRUE" or just TRUE without quotation marks. I wonder what is wrong with this ? Is there a way to rewrite the function?

NOTE:

  • Data_Entry tab is where I fill the data of patients.
  • All_Patients tab is generated by query function (view only) from Data_Entry tab.
  • All_Patients tab Column I represents the column H in Data_Entry tab when the checkbox value is TRUE.
0

1 Answer 1

2

You are using the RangeList.clearContent() method to remove data. This makes the value in the checkbox cells null instead of false or true.

The query() function only accepts one data type per column. The type of a column is determined by the majority of the values in the beginning of the column. When you clear the data, the data type of column Data_Entry!H:H is set to null, which means that the values that are true will not match the column type and will be considered null by query().

There are two possible solutions:

  1. Modify the ClearContents() function that clears the Data_Entry tab so that the checkboxes are set to false instead of setting them to null.

  2. Replace your query() functions with filter(), like this:

All_Patients!B5:

=iferror( 
  sort( 
    filter( 
      { 
        Data_Entry!B5:B, Data_Entry!O5:O, Data_Entry!D5:E, Data_Entry!L5:L, Data_Entry!J5:K, Data_Entry!H5:H
      }, 
      Data_Entry!B5:B <> "Ward", 
      not(isblank(Data_Entry!F5:F)) + not(isblank(Data_Entry!G5:G)) 
    ) 
  ) 
)

Possible_Discharge!D7:

=iferror( 
  sort( 
    filter( 
      All_Patients!B5:H, 
      not(isblank(All_Patients!B5:B)), 
      All_Patients!I5:I = true 
    ) 
  ) 
)
3
  • Clearing contents will make values to false and won't delete checkboxes Jun 13, 2021 at 20:15
  • 1
    Edited the answer to explain why .clearContent() makes the query() fail. Additional note: in Trigger.gs, the onEdit(e) function in broken and will generate an error every time any cell is edited. I think you can fix it by removing the ; ClearContents.sheet bit. Jun 13, 2021 at 21:19
  • That's great . Thank you alot Jun 14, 2021 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.