I built a sheet that helps me manage my Trivia forms by clearing all input data and resetting validation. It works great, I'm happy with it, but now I want to update it to also Open and Close the Google Forms. I already have the script written, it works perfectly fine, but I'd like to have a cell underneath the Toggle that shows if the round is currently "Open" or "Closed" so I know if it needs to be toggled or not.

I couldn't find any formulas to directly access whether or not it's accepting responses, so I thought maybe I could use the XML formula to check the HTML for "This form is no longer accepting responses," but I'm getting a parse error on that. And if there's a way to check it directly, without having to peek at the HTML, that'd be ideal.

=IF(IMPORTXML(GOOGLEFORMURL,"//div[@class='freebirdFormviewerViewResponseConfirmationContent']")="This form is no longer accepting responses","Open","Closed")

Attached image shows the existing sheet - I just want to add a fourth row of buttons that, like the existing ones, show the current status.

CodePudding user response：

if the form is closed then URL:

https://docs.google.com/forms/d/1jazSba1GqY0yIH6Srv-HXcUsH0d5cBJ5V2D9Z1uQt6w

will autocorrect to:

https://docs.google.com/forms/d/1jazSba1GqY0yIH6Srv-HXcUsH0d5cBJ5V2D9Z1uQt6w/closedform

and can be detected as:

=QUERY(FLATTEN(IMPORTDATA(A1)); "where Col1 contains 'docs-crp'"; )

where A1 is the URL of the form you shared

UPDATE:

=REGEXMATCH(QUERY(FLATTEN(IMPORTDATA(A1)), 
 "where Col1 contains 'docs-crp'", ), "closedform")

(bound to the checkbox to force a refresh on demand)

then opening the form like:

CodePudding user response：

Update - player0's answer IS working, but it doesn't update in real time. That's why I originally thought it wasn't working. Even if I try to force an update, it doesn't change. It only updates at a rate that appears to be a couple times an hour.

So while this is technically solved, if there's any way to get real time updates, I'd appreciate it.