- deliverygurullcAsked on August 15, 2015 at 03:20 PM
What I am referring to is not clearing a field but rather not using a field at all and when submitted the end result is a cell available for first time use like any other unused cell on a spreadsheet and not containing any value hidden or otherwise. I have hidden fields on my form that are there for the sole purpose of claiming sheet space for use after submission. The problem I am experiencing is that when the form is submitted the fields contain some sort a value that is prohibiting the intended sheet work from automatically taking place. To be more specific this invisible value is not allowing arrayformulas to operate without going to the cell in question after every submission to manually clear out the already empty cell as well as the error created because of this problem
- deliverygurullcAnswered on August 15, 2015 at 03:32 PM
Also to add to that. As I recall, if I am not mistaken, this occurrence of the ghostly value is something relatively new. I have gone back and forth between using the form field at form start for the data desired (i.e. Order Time) to deactivating the field from the form and generating time from within the sheet automatically upon submission. Clearing of the already empty field was never needed until recently. How can I send an truly empty field is my question?
- deliverygurullcAnswered on August 15, 2015 at 03:41 PM
Apparently the cell in question contains the value of a label type
- JotForm SupportEltonCrisAnswered on August 16, 2015 at 02:34 AM
I believe that's not possible with the Google Spreadsheet Integration. Here's my workaround instead which hopefully would work base on your requirements.
Please take note that we do not recommend editing or making changes on the sheet where the submissions go. We suggest to add another sheet instead on the same spreadsheet file and do all the customization you want on the data over there.
1. Add a new sheet
2. Use array formula =ArrayFormula(Submissions!A1:A) formula on the first cell of the new sheet and drag it to the rest of the first row cells to automatically copy all the values and upcoming ones from the submissions sheet to the new sheet. Here's a screenshot:
Hope this helps. If I have missed anything here or if this doesn't fit your requirements, feel free to let us know. Thanks!
- deliverygurullcAnswered on August 16, 2015 at 03:23 AM
Thank you EltonCris, I appreciate your feedback sir. I am familiar with this particular method of usage with Arrayformula. This is what I used before I learned about the Query function. It has been a while and I will give that a try. Pardon me for saying so but isn't that no different really than using the Query function? I have, at the least, worked out a temporarily solution that I hope will be long term but after this week plus long fiasco I am definitely in favor of actively searching for that backup plan if nothing else. Unless things have changed with how this function operates, from what I remember about using this as a work around with a form, or with Query and Importrange with a form, unless there is a person editing the sheet from time to time it will eventually after so long stop being automatically reliable. However, I will give this a try to find out for sure. One last thing sir. Is the the newly proposed solution for the known problem people are having or is this just one of those, "hey give this a try" things? Regardless, thanks again sir!
- JotForm SupportEltonCrisAnswered on August 16, 2015 at 05:40 AM
You're welcome. :)
I'm not really good with spreadsheet formula but from what I can see, query function can work the same as the arrayformula. They may differ a few things since base on my research, query can be used the same as SQL but with a few feature of its own. Whichever work better for you, you can give it try. So far base on my test, it updates the sheets automatically without opening it every time after they are properly setup.
This is just one of thing, and personally I think this is the best workaround so far. My colleagues may have their own idea too but it's most likely similar to this. The good thing is, we usually provide workarounds to the user as much as we can.
- deliverygurullcAnswered on August 16, 2015 at 05:50 AMThat's why I'm still here EltonCris. I think you will find that after say 2
or 3 hours and especially if the workbook is not open that it will freeze
and maintain a fixed view at some point. My situation is extremely unique
being that we have our mobile platform that requires the first tab position
in the workbook as well.
- deliverygurullcAnswered on August 16, 2015 at 06:00 AMI can't seem to find the documentation or get it to run
- deliverygurullcAnswered on August 16, 2015 at 06:10 AMWrong thread sorry
- JotForm SupportEltonCrisAnswered on August 16, 2015 at 01:56 PM
No worries. :)
So I made another test at this moment and everything is still working.
Here's a GIF that shows my test.
- deliverygurullcAnswered on August 16, 2015 at 02:25 PMExcuse me, EltonCris sir, I hate to be the one to tell you this but your
test is inconclusive sir. Every click you make on a cell is restarting your
test. We know the formula works on form submissions and that's obviously
important. So then the real way to test this would be to have the formula
- deliverygurullcAnswered on August 16, 2015 at 03:35 PM
It just occurred to me that what I saying would have to done with importrange which we already know is a not going to pass that test. So same workbook underwater with a camera inside to monitor it without opening it. That should do the trick sir
- JotForm SupportjonathanAnswered on August 16, 2015 at 08:25 PM
If my understanding of your latest response is correct, it seems you have found a working solution already.
If you need further assistance, please let us know.
- deliverygurullcAnswered on August 16, 2015 at 09:05 PMYes sir, a temporary working solution but who knows maybe it will last.
Regardless, after over a week with no form integration I am and will be
looking at every back up option so that when this happens again we can
continue business as usual and seemlessly without missing a beat. What I
think I may have, could very well be nothing but a temporary horseshoe. It
is actually more likely that it is exactly that than not. In fact Jonathan,
every free moment that I have right now is spent on this problem just the
same as it was since the first Friday the connection became broken. The