Basic Google Spreadsheet questions

Ok, I've read the FAQ here: http://www.chronoengine.com/faqs/70-cfv5/5233-how-do-i-use-the-google-spreadsheet-save-action.html
I've read various threads on these forums, and I have no idea what to do to make my form save to a spreadsheet. I'm sorry, but the explanations are not specific enough.

The FAQ says "Second create you form and use Array Names for the inputs that you need to save. I used the suggested 'GSheet' model ID and so my inputs are called GSheet[text1], GSheet[text2] and GSheet[text3]. "
Where do I do this? Do I enter this in Field ID within my text box configuration? I tried setting it to GSheet[FirstName], where the first column is defined as FirstName, and nothing happened. There are no elements that mention array, so that was the most logical place to put it. I also tried placing GSheet[FirstName] in the field name and that didn't work. Where else does it go if not there?

And then, once that part is resolved, what am I actually typing into the Google Spreadsheet Save area? I have it enabled, with my username, password, the name of the spreadsheet and worksheet. Those seem straight forward. But the Data Path makes no sense. The examples shown don't help at all. I tried putting in the GSheet[FirstName] there as well. I'm completely lost at this point, and this is the only thing holding up my forms at this point. Please clarify.

Hi,

The data path in the GS action should match the model id, which is "GSheet" in your case!

The "GSheet[FirstName]" should be your "field name".

Regards,
Max

Thank you for answering the field name question. That solves half the problem. :-)

Now, I'm still confused on what I should be typing in the action.

If it just says GSheet, that doesn't do anything. And it doesn't do anything if it says GSheet[FirstName]. What am I missing?

Side note, I don't receive an errors upon submission either. I do receive my other actions of loading the paypal button and receiving an email of the form. So it's only the spreadsheet save that's holding me up.

Also, since I changed every field name over to the GSheet[name] the entries into those fields no longer are sent in the email. I did an auto generate for the template, so it updated to whatever I changed the field names to. I get the email, but all the responses are blank. Now I'm really confused.

I finally added a debugger and it says:
[4] => Array
(
[Google Spreadsheet Save] => Array
(
[Authentictaion] => Failed
)

Since I've verified I have the correct email and password, is it possible having two step authentication on my google account is causing this to fail? I'm not sure if not many people are using this feature, or if not many have had this problem, since searching the forums brings up nothing.

Hi butchjax,

In the EMail template you need to use {GSheet.input_name}

Please drag a Debugger action into the On Submit event, then submit the form and post the debug - including the 'dummy emails' results here.

Bob

Data Array

Array
(
    [option] => com_chronoforms5
    [chronoform] => 2015_Gathering_Attendance_Registration
    [event] => submit
    [Itemid] => 
    [GSheet] => Array
        (
            [FirstName] => Jackie
            [LastName] => Meyer
            [Email] => jackie.meyer@gmail.com
            [AltContact] => 
            [JediName] => 
            [JediOrders] => 
            [Address1] => 6105 South Parker Rd Apt 5107
            [Address2] => APT 5107
            [City] => Centennial
            [State] => CO
            [Country] => United States
            [Zip] => 80016
            [Phone] => 9209156220
            [NumAdults] => 2
            [NumChildren] => 
            [Likelihood] => 2
            [Travel] => Array
                (
                    [0] => Driving
                )

            [Commute] => 
            [Seminar] => 
            [Dates] => Array
                (
                    [0] => Jun 12
                    [1] => Jun 13
                )

            [Success] => sdfgearg
        )

    [button11] => Submit
    [7ef966141cb34927e3320d584e0e480fafa131da] => 1
    [ip_address] => 208.185.19.106
)
 
Errors
Array
(
)

Debug Info
Array
(
    [21] => Array
        (
            [Check Honeypot] => Array
                (
                    [0] => Honeypot check passed.
                )

        )

    [4] => Array
        (
            [Google Spreadsheet Save] => Array
                (
                    [Authentictaion] => Failed
                )

        )

    [7] => Array
        (
            [Email] => Array
                (
                    [0] => An email with the details below was sent successfully:
                    [1] => To:jackie.meyer@gmail.com
                    [2] => Subject:Colorado Jedi Gathering Registration
                    [3] => From name:First_Name
                    [4] => From email:admin@instituteforjedirealiststudies.org
                    [5] => CC:
                    [6] => BCC:
                    [7] => Reply name:
                    [8] => Reply email:
                    [9] => Attachments:
                    [10] => Body:
<table>
<tr><td>First Name</td><td></td></tr>
<tr><td>Last Name</td><td></td></tr>
<tr><td>Email Address</td><td></td></tr>
<tr><td>Alternate Contact Info</td><td></td></tr>
<tr><td>Jedi Name</td><td></td></tr>
<tr><td>Jedi Order(s) Membership</td><td></td></tr>
<tr><td>Street Address</td><td></td></tr>
<tr><td>Street Address</td><td></td></tr>
<tr><td>City</td><td></td></tr>
<tr><td>State</td><td></td></tr>
<tr><td>Country</td><td></td></tr>
<tr><td>Zip or Country Code</td><td></td></tr>
<tr><td>Phone Number</td><td></td></tr>
<tr><td>Number of adults</td><td></td></tr>
<tr><td>Number of children attending</td><td></td></tr>
<tr><td>How likely are you to attend?</td><td></td></tr>
<tr><td>Method of Travel</td><td></td></tr>
<tr><td>If travelling with others, who are they?</td><td></td></tr>
<tr><td>If you feel qualified to lead a seminar or activity, please share in this box.</td><td></td></tr>
<tr><td>What dates do you expect to attend?</td><td></td></tr>
<tr><td>This gathering will be a success for me if ____ (answer in the box)</td><td></td></tr>
</table><br /><br />IP: 208.185.19.106
                )

)

)

When I look at the auto generated email template, the tables have this format:
<tr><td>First Name</td><td>{GSheet[FirstName]}</td></tr>
<tr><td>Last Name</td><td>{GSheet[LastName]}</td></tr>
<tr><td>Email Address</td><td>{GSheet[Email]}</td></tr>

If it should be {GSheet.FirstName} instead, I would think that's a bug in the auto generator. Is that the case?

And the emails come through like this.

First Name
Last Name
Email Address
Alternate Contact Info
Jedi Name
Jedi Order(s) Membership
Street Address
Street Address
City
State
Country
Zip or Country Code
Phone Number
Number of adults
Number of children attending
How likely are you to attend?
Method of Travel
If travelling with others, who are they?
If you feel qualified to lead a seminar or activity, please share in this box.
What dates do you expect to attend?
This gathering will be a success for me if ____ (answer in the box)

Yes, that's a bug in the email template generator, which I will need to fix!🙂

Aside from this, did you manage to fix the issue with saving to the sheet ? you can add a debugger action after the sheet save action to check the data sent.

Regards,
Max

Well, the debugger says the authentication is failed. (authentication is misspelled but if you don't mind, I don't mind. lol)

Do I need to put in more than i debugger to the On Submit action area? It seems to pick up everything in it.

1 debugger is enough, it will list everything before it!

So, this means your username/password are incorrect ? or you don't have curl enabled on your PHP ?

Thanks for pointing to the typo, I fixed that!🙂

I need curl? I didn't do anything with curl because I don't know what it is, and haven't seen anything mentioning that it is needed.

My username and password are correct. I even logged out of gmail to make sure I hadn't changed it and forgotten. :-) I'm not sure if having two step authentication could screw it up though.

On the plus side, manually editing the email template brought back my email information. Woohoo! That at least allows me to manually transfer information from the emails to the spreadsheet.

There is one error in the debugger. At the end, there's an empty array. I'm not sure where this could be coming from so I don't know how to remove it.

Hi butchjax,

cURL is used by the CF code to send the data over to Google. It's implemented in most commercial PHP installations. You can check on the Site Admin | System Info | PHP info page.

Not sure about double authentication, I think I have it enabled but didn't have any problems saving to Google.

Bob

No dice. Should curl be before or after the google save? I've tried it both ways and received the same results. I've also turned on sharing in the spreadsheet, in case that could have caused a problem.

I suspect the problem has to do with this array error. It comes up under Errors.

Here's my current state:

Data Array
Array
(
    [option] => com_chronoforms5
    [chronoform] => 2015_Gathering_Attendance_Registration
    [event] => submit
    [Itemid] => 
    [GSheet] => Array
        (
            [FirstName] => Jackie
            [LastName] => Meyer
            [Email] => jackie.meyer@gmail.com
            [AltContact] => 
            [JediName] => 
            [JediOrders] => 
            [Address1] => 6105 South Parker Rd Apt 5107
            [Address2] => APT 5107
            [City] => Centennial
            [State] => CO
            [Country] => United States
            [Zip] => 80016
            [Phone] => 9209156220
            [NumAdults] => 2
            [NumChildren] => 
            [Likelihood] => 1
            [Travel] => Array
                (
                    [0] => Flying
                )

            [Commute] => 
            [Seminar] => 
            [Dates] => Array
                (
                    [0] => Jun 14
                )

            [Success] => asdf
        )

    [button11] => Submit
    [83fc4f6b3f5e53c112e1f35b25eba0422615a3ae] => 1
    [curl] => <!DOCTYPE html><html lang="en" ><head><meta name="description" content="Web word processing, presentations and spreadsheets"><link rel="shortcut icon" href="//ssl.gstatic.com/docs/common/drive_favicon1.ico"><title>Google Drive -- Page Not Found</title><link href="//fonts.googleapis.com/css?family=Open+Sans:300" rel="stylesheet" type="text/css"><style>/* Copyright 2014 Google Inc. All Rights Reserved. */
.goog-inline-block{position:relative;display:-moz-inline-box;display:inline-block}* html .goog-inline-block{display:inline}*:first-child+html .goog-inline-block{display:inline}#drive-logo{color:#91959c;font-family:"Open Sans",Arial,sans-serif;font-size:27px;font-weight:300;position:absolute;text-shadow:0 1px 1px white;white-space:nowrap}#drive-logo img{padding:0 0.4em 0 0;position:relative;top:2px;vertical-align:middle}#drive-logo a{color:#91959c;text-decoration:none}#drive-logo span.goog-inline-block{margin-top:2px;vertical-align:top}</style><style type="text/css">body {background-color: #fff; font-family: Arial,sans-serif; font-size: 13px; margin: 0; padding: 0;}a, a:link, a:visited {color: #112ABB;}</style><style type="text/css">.errorMessage {font-size: 12pt; font-weight: bold; line-height: 150%;}</style></head><body><div style="margin: auto; max-width: 750px;"><div style="margin: 80px 40px 20px 40px; position:relative; "><div style="position: absolute; top: -80px;"><h1 id="drive-logo"><a href="/"><img src="//www.google.com/images/logos/google_logo_41.png" width="116" height="41" alt="Google logo" ><span class="goog-inline-block">Drive</span></a></h1></div><div align="center"><p class="errorMessage" style="padding-top: 50px">Sorry, the file you have requested does not exist.</p><p> Please check the address and try again. </p><div style="background: #F0F6FF; border: 1px solid black; margin-top: 35px; padding: 10px 125px; width: 300px;"><p><strong>Get stuff done with Google Drive</strong></p><p>Apps in Google Drive make it easy to create, store and share online documents, spreadsheets, presentations and more.</p><p>Learn more at <a href="https://drive.google.com/start/apps">drive.google.com/start/apps</a>.</p></div></div></div></div></body></html>
    [ip_address] => 208.185.19.106
)
Array
(
)

Errors
Array
(
)

Debug Info

Array
(
    [21] => Array
        (
            [Check Honeypot] => Array
                (
                    [0] => Honeypot check passed.
                )

        )

    [4] => Array
        (
            [Google Spreadsheet Save] => Array
                (
                    [Authentictaion] => Failed
                )

        )

    [25] => Array
        (
            [Curl] => Array
                (
                    [0] => CURL OK : the CURL function was found on this server.
                    [1] => $curl_values: 
                    [2] => curl_target_url: https://docs.google.com/spreadsheets/d/1od9kKud74ic8g81-N3kYUKsTIdUrQKZJS3DDE8iLaSk/edit?usp=sharing
                    [3] => curl_errors: 
                    [4] => curl_info: Array
(
    [url] => https://docs.google.com/spreadsheets/d/1od9kKud74ic8g81-N3kYUKsTIdUrQKZJS3DDE8iLaSk/edit?usp=sharing
    [content_type] => text/html; charset=utf-8
    [http_code] => 405
    [header_size] => 1102
    [request_size] => 199
    [filetime] => -1
    [ssl_verify_result] => 0
    [redirect_count] => 0
    [total_time] => 0.285767
    [namelookup_time] => 0.004138
    [connect_time] => 0.004737
    [pretransfer_time] => 0.033735
    [size_upload] => 0
    [size_download] => 2186
    [speed_download] => 7649
    [speed_upload] => 0
    [download_content_length] => -1
    [upload_content_length] => 0
    [starttransfer_time] => 0.254805
    [redirect_time] => 0
    [redirect_url] => 
    [primary_ip] => 74.125.224.206
    [certinfo] => Array
        (
        )

    [primary_port] => 443
    [local_ip] => 72.34.36.225
    [local_port] => 43728
)

                )

        )

    [7] => Array
        (
            [Email] => Array
                (
                    [0] => An email with the details below was sent successfully:
                    [1] => To:jackie.meyer@gmail.com
                    [2] => Subject:Colorado Jedi Gathering Registration
                    [3] => From name:First_Name
                    [4] => From email:admin@instituteforjedirealiststudies.org
                    [5] => CC:
                    [6] => BCC:
                    [7] => Reply name:
                    [8] => Reply email:
                    [9] => Attachments:
                    [10] => Body:
<table>
<tr><td>First Name</td><td>Jackie</td></tr>
<tr><td>Last Name</td><td>Meyer</td></tr>
<tr><td>Email Address</td><td>jackie.meyer@gmail.com</td></tr>
<tr><td>Alternate Contact Info</td><td></td></tr>
<tr><td>Jedi Name</td><td></td></tr>
<tr><td>Jedi Order(s) Membership</td><td></td></tr>
<tr><td>Street Address</td><td>6105 South Parker Rd Apt 5107</td></tr>
<tr><td>Street Address</td><td>APT 5107</td></tr>
<tr><td>City</td><td>Centennial</td></tr>
<tr><td>State</td><td>CO</td></tr>
<tr><td>Country</td><td>United States</td></tr>
<tr><td>Zip or Country Code</td><td>80016</td></tr>
<tr><td>Phone Number</td><td>9209156220</td></tr>
<tr><td>Number of adults</td><td>2</td></tr>
<tr><td>Number of children attending</td><td></td></tr>
<tr><td>How likely are you to attend?</td><td>1</td></tr>
<tr><td>Method of Travel</td><td>array (
  0 => 'Flying',
)</td></tr>
<tr><td>If travelling with others, who are they?</td><td></td></tr>
<tr><td>If you feel qualified to lead a seminar or activity, please share in this box.</td><td></td></tr>
<tr><td>What dates do you expect to attend?</td><td>array (
  0 => 'Jun 14',
)</td></tr>
<tr><td>This gathering will be a success for me if ____ (answer in the box)</td><td>asdf</td></tr>
</table><br /><br />IP: 208.185.19.106
                )

        )

)

You don't need an extra cURL action, but the cURL library should be installed and enabled on your server, because its used by the GS action, please check it here:

You can check on the Site Admin | System Info | PHP info page.

While that information is not clear to me - there's no navigation like that in joomla or cpanel, I contacted my webhost and they confirmed that curl is installed already, and has been.

This is getting frustrating. This software is touted as being easy to use, and yet we've gone back and forth many times and not gotten this to work (though we have solved a few things, which I appreciate). Maybe it's easy for someone who has a lot of experience with arrays and such, but I don't have that specific experience. And while I've been working on my website for a few years now, and have some programming experience, this isn't clear enough to follow. :-( A lot of basic information is missing from the FAQs.

1. If I need to use curl, what address do I point it to? the generic drive.google.com? Or the specific address of the spreadsheet I created?

2. Any idea why there is an array error in the debug report? I can't find where it could be coming from, it seems to just be a random extra array at the bottom of the form. Since I don't see it obviously in the code I can't manually edit it out to see if that would help.

3. In the google spreadsheet action, under Data Path, do I need to have more information than just GSheet? Or do I need to write out all of the elements in there as well?

4. What else can cause an authentication failed message for the google save if the email and password have been verified multiple times as correct? I don't know what else to check at this point.

5. Has anyone gotten this to work properly?

I'm not trying to be pissy, but there's a communication issue here, and I'm trying to lay it out more clearly. This basic information will help others, not just me. As more people are using v5 this will become more of an issue.

The curl is used by the GS action to communicate with Google, so you don't have to worry about this part at all, I just thought you should check its on your server, because if its not then the connection will fail.

The Authentication failed message means that the action tried to connect to google with your credentials and it failed, so I can't suggest anything but to double check the login data, and that your curl doesn't have a white list or something blocking it from communicating with google.

Regards,
Max

Are you unable to answer the other questions, like #2 or 5?

#2- there are no errors in the debug, just an empty array, and this is related to the fields errors, and its empty which means no fields have any errors!

#5- yes, I had it working when I made it, Bob also mentioned that he has it working, and few other users have used it and posted their questions on the forums, so its working.

My webhost says there is no such thing as a whitelist or blacklist or anything for curl.

#2 Oh! Nice. :-) It's not intuitive, but it's good to see!

I have no idea why this isn't working for me then. I logged out of google twice and made sure I had the right email and password.

No spaces after your email/password ? please try to use the username only, not the full email address.

If it still fails then you can try the following:

open the speadsheet.php file under this path:

\administrator\components\com_chronoforms5\chronoforms\actions\google_spreadsheet_save\

After line 32:

 $response = curl_exec($curl);

Add this line:

pr($response);

Now submit the form once and send the extra output you get, pay attention to hide any sensitive data!

Doing that gives me these two lines.

Error=BadAuthentication
Info=InvalidSecondFactor

To me that sounds like a two factor authentication issue, but that's a wild guess.

I turned off 2 step verification and it ended up with these errors. I will try setting up an app specific password, but it seems like this is maybe a deeper issue with google.

Error=BadAuthentication
Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbueU7by3-TtGyphr3cd0rEVCP-KUaagLzp4mYbveQdY4SRDUazwcfsT09Pfr1vKRX8XgSmOGrxO0rdNMHsSqVhxzI3ENXzniU-r7GXTpY941LAu174EoA4wlXsh9rd0sZXgURWeLS3pXDKVC5E196uJd7BngjN7gwQUU5R9WU_mMFHow_gTNB-sdP9JKQ7RxTpPcZghx84thmQF20EQEKQWNJne0w
Info=WebLoginRequired

Success!

I turned 2 step verification back on. I ran it, the response was all weird on the page, but the debugger showed it worked, and I see the data in the spreadsheet! I even made sure a second submission worked. Now to apply these fixes to the second form and I'm all set!

Can someone please add this information to the FAQ? I would hope most people are using 2 factor verification and this would be crucial to making their spreadsheet work. :-)

Great news, so, you have used your google account password or a password specific for the google drive app ?

I had to use an app password for it to work. Just turning off 2 step verification didn't work. Which seems weird, but whatever. It works with an app password, and it worked for both of my spreadsheets. It just takes a little digging to find that part of the account settings.

Thank you, added this to the FAQ

Regards,
Max