Google Spreadsheet/Form Integration with date limits?

  • 1
  • Idea
  • Updated 3 years ago
  • Not Planned
Perhaps someone can help make this idea happen.

Here's a sample presentation that uses the Spreadsheet gadget to scroll information we want to share.

This is a Google Form makes it very easy to allow people to enter information which shows nearly immediately after it's entered.

(For typical use, the form would require people to sign in and changes would be logged directly from the spreadsheet just in case. They would also get an email link to make edits or changes for that submission)

So far, this works pretty well, I think, especially for a small non-profit like I have here.

What would make this amazing, however, would be to to include the ability to use the start and ending dates on the Google form -- so the messages appear and are removed based on those dates.

Unfortunately, I'm not technically proficient enough to figure this one out...any ideas?
Photo of Biologyben

Biologyben

  • 3,558 Points 3k badge 2x thumb
  • intrigued

Posted 5 years ago

  • 1
Photo of Neal

Neal

  • 61,252 Points 50k badge 2x thumb
Interesting thinking.

As far as I can tell, the start and end dates are essentially just fields recorded to the spreadsheet (no different than what the scrolling text would be) and don't have any direct involvement with making changes in the Presentation editor.
Photo of Biologyben

Biologyben

  • 3,558 Points 3k badge 2x thumb
Correct - That is the question/problem. How to limit the display of those items in the spreadsheet within the date limits.

One option may be a script on the spreadsheet itself that moves an expired message to a sheet called 'deleted', and early messages to a 'upcoming' sheet. Meanwhile, the display only uses the 'current' sheet. That way, we'd have an audit trail if needed.
Photo of Biologyben

Biologyben

  • 3,558 Points 3k badge 2x thumb
Much more easy than I thought. Open a new sheet (we'll call it Current Text) on the form submission spread sheet and add this formula (I used cell A2 with a header row)

=filter('Sheet1'!A2:E; 'Sheet1'!E2:E<=today();Sheet1!D2:D>=today(); 'Sheet1'!C2:C<>ʺʺ)

This assumes that your form submits data to Sheet1 in columns A-E starting on row 2; that column E has the start date and Column D has the end date. Column C should have the message. Modify to suit.

This brings a dynamic copy of the data to the to the new sheet. Hopefully, we can pull the message from the correct column from the new spreadsheet. I'm getting RCP Failed messages currently, but I'll try to make the changes to see if it works tomorrow.
Photo of Neal

Neal

  • 61,252 Points 50k badge 2x thumb
Good to hear you've found a solution.

Regarding the RPC failure, can you try clearing your cache to see if that clears the issue for you?

Thanks.
Photo of Biologyben

Biologyben

  • 3,558 Points 3k badge 2x thumb
So this kind of works in concept - feel free to test above. One last hurtle to overcome and it seems to be back to RV - On dynamically generated spreadsheet using the formula above, it runs once, but does not repeat.

In the spreadsheet I'm using, I created a conditional formatting rule to mark empty cells black - All the cells show up black so there are not spaces or anything - that should not be a concern.

Theories?
Photo of Neal

Neal

  • 61,252 Points 50k badge 2x thumb
When you're adding the spreadsheet, are you using the entire sheet or a range of cells?

Could it be that if using a range, you're outside of the range on the 2nd attempt?
Photo of Biologyben

Biologyben

  • 3,558 Points 3k badge 2x thumb
Originally the range was selected with C2:C100 (I might have also tried C2:C). I changed the spreadsheet settings to use the new dynamic sheet without changing the range. So the only change made was to change the sheet used from Sheet1 to the current message sheet (which dynamically makes a copy of the first sheet on the current messages sheet)

Strangely, if I completely delete the empty cells, it does work as expected on the new sheet. We'll see if when the message drop off if the rows persist and cause a problem.

The workbook is here:

https://docs.google.com/spreadsheet/c...
Photo of Neal

Neal

  • 61,252 Points 50k badge 2x thumb
I wonder if it is showing all of the cells because they are filled. If you take the data source URL and paste that into a browser, it should show the same info. It probably has to do with the API we are using from Google. If that is the case then unfortunately not much can be done about it.
Photo of Biologyben

Biologyben

  • 3,558 Points 3k badge 2x thumb
Good to know about the URL - That will help in troubleshooting later. Thanks for your help!

One last note: For my own sanity, you'll notice that I made two additional sheets discussed above for upcoming messages and expired messages to keep things straight when reviewing - this has no effect on the RV presentation, but does allow me to keep track of everything easier.

The upcoming messages code is:
=filter(Form_Results!A2:E; Form_Results!E2:E>today(); Form_Results!C2:C<>"")

The expired messages code is:
=filter(Form_Results!A2:E; Form_Results!D2:D<today(); Form_Results!C2:C<>"")

Those might be useful if you wanted to change this to an 'upcoming events' list or what not.
Photo of Biologyben

Biologyben

  • 3,558 Points 3k badge 2x thumb
Just an FYI as a follow up. If I don't mess with the spreadsheet, this works well. Even when the number of messages reduce from the day before.

Hope this helps!
Photo of Robb

Robb, Official Rep

  • 76,676 Points 50k badge 2x thumb
Thanks for the followup Ben!
Photo of CedarNate

CedarNate

  • 434 Points 250 badge 2x thumb
Ben, would you be willing to share the HTML page you used with the ticker and would it be ok if I took what you created and modified it? Since there seems to be no way to directly message someone on here, my email address is "ndelano@cedarburg.k12.wi.us". Thank you.
Photo of Biologyben

Biologyben

  • 3,558 Points 3k badge 2x thumb
I think the HTML page you are refering to is a Google Form - This is created by google.  I see your school uses Google Apps - so this should be as easy as going into your drive and creating a form for your users to complete.

In the spreadsheet of the form - you will get responses and the beauty of the system is that all responses are tracked and saved indefinitely.  If someone creates something to be shown for a short time and someone complains about it, you can go back and see the original submission, who made it and at what time.

One issue that is new is that Rise does not have a horizontal scroll for the spreadsheet widget like they did for the gadget.  This makes the implementation slightly different, but it is still the easiest way to have non-skilled users quickly add info to your screen.
Photo of CedarNate

CedarNate

  • 434 Points 250 badge 2x thumb
Thank you for your reply. I understand how the Google Form and Google Doc works. What I don't understand is how the Rise Vision plugin/widget works. In a separate thread I was linked a guide that required a hosted web page. https://community.risevision.com/rise_vision_inc/topics/introducing-the-google-spreadsheet-component... The guide does not reference how to do scrolling text. I do not understand how you get from a Google Sheet (with a form) to scrolling text content in a sign.
(Edited)
Photo of Biologyben

Biologyben

  • 3,558 Points 3k badge 2x thumb
Oh - I see.  Sorry for my confusion.  I set this up before web components, widgets and all the new stuff Rise has added.  

I have never messed with the web components, so I cannot help.  I did follow the instructions to install the gadget from Git and installing as a custom gadget using my webpage server as the host for the files.

https://community.risevision.com/rise_vision_inc/topics/how-to-add-a-custom-gadget-to-your-company
Photo of CedarNate

CedarNate

  • 434 Points 250 badge 2x thumb
No worries. What you just described is exactly what I want to do. Would you be willing to share those hosted files or send me a copy of them?
Photo of Biologyben

Biologyben

  • 3,558 Points 3k badge 2x thumb
If you follow the instructions on the link I posted above, step one says, "1. From your PC, place all the files for your Gadget in a folder. You can download the Spreadsheet Gadget from here as an example."

The here includes a .ZIP archive of all the files you need.