Power Tool Hidden Box

  • Profile Image
    jodyb
    Asked on September 02, 2012 at 04:04 PM

    Hello, I am trying to use the power tool 'Hidden Box' to bring functions into my Google Spreadsheet.  If I use any type of concatenate function, JotForm knocks off any part of the formula following a comma or the & sign.  In other words, if I want a function such as fullname=firstname&lastname, I get fullname=firstname& when it integrates to google spreadsheet.  If I try to use fullname=concat(firstname,lastname), I get fullname=concat(firstname, when it imports to google spreadsheet.  Is there a solution or workaround for this problem?

  • Profile Image
    Deygus
    Answered on September 02, 2012 at 04:50 PM

    Hi, it basically sounds like you are pretty much trying to precode your own method of JotForm to Google Docs. We already have a formidable answer that is easier and already there for you to use. Please refer to this guide: http://www.jotform.com/blog/57-Google-Docs-Integration-Send-Responses-to-Google-Spreadsheets-Instantly-. If you have more questions or need more help we're only a few keyboard strokes away!

  • Profile Image
    Deygus
    Answered on September 02, 2012 at 05:02 PM

    Also, the Hidden Box is more intended for onClick actions that are hidden from the user that would cause an Event to happen such as BOX B appears all of a sudden in the Second Input Field because BOX A had an option in it that when it was selected by user it would cause it to appear to gather more information and such from them for that particular form field. Hope this helps but if you need more help let us know so that we can assist you further.

  • Profile Image
    jonathan
    Answered on September 02, 2012 at 05:33 PM

    @jodyb

    Hi, please note that any modification applied on the integrated spreadsheet (such as formulas), will not be read by the spreadsheet on the same sheet of the form submissions. You will noticed this when you applied a formula function within the 'submissions' sheet, the rows with modification or with formula will be skipped.

    Example, if I have 7 submissions (7 rows in spreadsheet), if I applied a formula in row 8, then create new submission, the new submission in spreadsheet will be in row 9... it will skip row 8.

    Please refer to screen below...

     

    This my sample form http://jotform.me/form/22456142089454

    You can test it and submit, then you can check the google spreadsheet here 

    The working formula are in Sheet2.

    Hope this help. Please inform us if you have further inquiry on this.

    Thanks.

  • Profile Image
    jodyb
    Answered on September 03, 2012 at 07:19 AM

    I have determined that the problem is quotation marks.  Any quotation mark in a hidden field seems to truncate the field when it integrates to Google docs. In other words, if you wanted to say fullname=concat("the user's full name is ",firstname," ",lastname".")... JotForm cuts off everything after the first quotation mark, so in Google docs, it looks like fullname=concat(

    Do you guys know of a solution for this?

  • Profile Image
    jodyb
    Answered on September 03, 2012 at 08:55 AM

    Have you guys changed something on your end?  I have determined a solution to my problem, but now all of the formulas have an open parenthesis ( before the = sign.

  • Profile Image
    jodyb
    Answered on September 03, 2012 at 09:19 AM

    Hello.  We are almost there!  I have a solution, but now, this is what I am getting...

    for the field calcage, my default value is:

    =rounddown(max(1,12*(year(today())+month(today())/12-(year+month/12))))

    but, when it gets over to Google Docs, it looks like this:

    (=rounddown(max(1,12*(year(today())+month(today())/12-(year+month/12))))

    Notice the parenthesis at the beginning. Can you tell me how to get rid of that?

  • Profile Image
    idarktech
    Answered on September 03, 2012 at 10:39 AM

    @jodyb

    I can't seem to replicate that here. I have copied that code to my hidden box field and it shows the same code in gdoc spreadsheet after submissions.

    Is it possible to re-integrate that form to gdoc for a test? See if the same problem occurs on the new spreadsheet. 

    Thanks.