WordPress.org

Ready to get started?Download WordPress

Forums

Contact Form DB
[resolved] [Plugin: Contact Form 7 to Database Extension] Pull data in Google docs spreadsheet (53 posts)

  1. rmx101
    Member
    Posted 3 years ago #

    Is it possible to pull form data into a google spreadsheet without exporting / importing csv file each time? Guess I need to use Excel Internet Query but not sure how to do this via Google Spreadsheets. Any ideas? thanks!

    http://wordpress.org/extend/plugins/contact-form-7-to-database-extension/

  2. Michael Simpson
    Member
    Plugin Author

    Posted 3 years ago #

    An excellent question. I think the answer is "almost".

    Google Spreadsheet has a way of loading data in from another web page using the "importHTML" function (reference) and based on that, I would put the following in a cell:

    =ImportHtml("http://<your-site>/wp-admin/admin.php?page=CF7DBPluginSubmissions"; "table"; 3)

    BUT it doesn't work presumably because the Google spreadsheet needs to login to the WP Admin area to be able to access that page, and it does not pop up a dialog to let you log in like Excel does for Excel Internet Query.

    I looked on the web to see if there is some way to make Google authenticate but I could not find anything. If anyone knows a solution to this please let us know!

  3. rmx101
    Member
    Posted 3 years ago #

    Thanks for the quick reply! Will also look for a way to bypass login but doesn't look like anything obvious. My goal is to show hide saved columns to different people (can be wp or non-wp users) and also make status notes on each row.

  4. Michael Simpson
    Member
    Plugin Author

    Posted 3 years ago #

    I got to experimenting with writing scripts in Google Docs and I came up with something. Give this a try and let me know if it works for you:

    1. Download this file and open it in a text editor http://plugin.michael-simpson.com/download/Cf7ToDBGGoogleSS.js
    2. Create a new Google Spreadsheet
    3. Go to Tools menu -> Scripts -> Script Editor...
    4. Copy the text from the downloaded file and paste it into the Google script editor.
    5. Save and close the script editor.
    6. Click on a cell A1 in the Spreadsheet (or any cell)
    7. Enter in the cell the formula:
    =CF7ToDBData("siteUrl", "formName", "user", "pwd")
    Where the parameters are (be sure to quote them):
    siteUrl: the URL of you site, e.g. "http://www.mywordpress.com"
    formName: name of the form
    user: your login name on your wordpress site
    pwd: password

  5. Michael Simpson
    Member
    Plugin Author

    Posted 3 years ago #

    I have added explicit support to exporting to a Google Spreadsheet in version 1.4

  6. paul.mand3l
    Member
    Posted 3 years ago #

    It looks like there's a bug that stops the code from working with wordpress installs hosted in sub-directories. (e.g. http://www.thirdwavedance.com/mikeandreuel/).

    Also, the url in the script includes '/contact-form-7-db/', which gave a 404 error. Some sleuthing indicated that it should be '/contact-form-7-to-database-extension/', which worked.

  7. Michael Simpson
    Member
    Plugin Author

    Posted 3 years ago #

    Good catch, I was using a different dir for testing and put in the wrong thing. I will fix that path in the code.

  8. revolutionfrance
    Member
    Posted 3 years ago #

    Hello! First thanks for an amazing plugin, and thanks for the live data trick...Got it to work on subdomain after manually changing urls.

    Just some questions:
    1/How do I get it to update? Does it update automatically after some time?
    2/ It doesn't seem like I can use the data in the spreadsheet for calculations, like sums, etc. Any idea why?

  9. Michael Simpson
    Member
    Plugin Author

    Posted 3 years ago #

    1. I'm not sure on the internals of Google Docs. I think it updates when you open the doc. If you click in the formula cell editor then press Enter, it may re-evaluate the formula. I don't know if anything else triggers it.

    2. I had a field with a number in it and was able to create a formula in a blank cell that referenced the first cell and add +1. That worked for me. But when I tried "SUM()" over some cells it did not work (gave me 0). If I use the formula ISNUMBER() referencing the cell it returns FALSE, ISTEXT() returns TRUE. So I think the spreadsheet treats all the data as text, and SUM() is failing to implicitly convert the cells to numbers. As a work-around you can use a blank column and use VALUE() to convert each cell from another column to a number, then apply SUM on that new column.

  10. rmx101
    Member
    Posted 3 years ago #

    This is excellent news.. Having trouble though. Followed steps to make a new spreadsheet, insert & save the script (copy pasted js) and entered the formula in a cell. Result shows #ERROR! in the cell but oddly shows a ton of data (posts) as an overlay to the spreadsheet stuck to the left side, on top of the navigation bar of g.docs.
    Any ideas? I tried Safari & Firefox (on a mac)

  11. revolutionfrance
    Member
    Posted 3 years ago #

    Hello! Thanks for the answer! Have been trying to get =value to work, but with no success...I'm not an advanced spreadsheets user.

    How can I return the values of an entire column (except for header) and not just for individual cells?

    Or is there a script that would help making an intermediate spreadsheet with all the values?

    Else the export is kind of useless :-)

    Thanks again for a great plugin and for all you help!

  12. Michael Simpson
    Member
    Plugin Author

    Posted 3 years ago #

    I think the issue with =value() is where you put it. I see an issue in my spreadsheet where I put it in the last column which is blank and it does not work. But if I make a new column to the right of that, it does work. I think the SS is seeing that column as part of the import area so it does not let you overwrite it.

    Better: create a new SS tab, and set cells in the new tab equal to values in the first tab, for example "=Sheet1!A1" to reference cell A1 in the first sheet (assuming it is still named "Sheet1"). If column A is a column of numbers with a header, start in A2 of the new tab and put =value(Sheet1!A2). You would have to fill/paste that formula down A3 to A100 or as many rows to be sure you got all of the rows in Sheet1 with data in it (do more than you need). I don't know if there is a way to automatically have all rows have that formula.

    Finally, to sum all of column A on the sheet, pick a cell somewhere else, (say B1) and enter =sum(A:A)

  13. rmx101
    Member
    Posted 3 years ago #

    Getting odd results.. I did paste the formula in A2 but it returns a bunch of lines, here's a snippet from the end of data showing the the SS

    <script type=
    function wp_attempt_focus(){
    setTimeout( function(){ try{
    d = document.getElementById('user_pass');
    d.value = '';
    d.focus();
    } catch(e){}
    }
    }

    In the top cell, it starts out listing <head> <title> <meta> etc

  14. Michael Simpson
    Member
    Plugin Author

    Posted 3 years ago #

    That means it is not logging into your WordPress successfully. So you are getting the login webpage returned instead of the target page which has the data. Check your user name and password.

  15. revolutionfrance
    Member
    Posted 3 years ago #

    Hello msimpson!

    Thanks for the tips! I had actually gone as far as getting the values in another spreadsheet individually, but was wondering if there was an automatically have all of the values reported to that other spreadsheet.
    Manually updating the SS with the =value is not so appealing as I expect to have many rows and columns filled with lots of data :-)

    Any other ideas?

    Thanks a lot,
    Paul

  16. Michael Simpson
    Member
    Plugin Author

    Posted 3 years ago #

    I don't know if there one can do that short of creating a macro. I suggest searching/posting on the Google Docs forum http://www.google.com/support/forum/p/Google+Docs. If you do, I am interested to learn what you find out.

  17. revolutionfrance
    Member
    Posted 3 years ago #

    Hello there! Wow, took me some time to find out, but found a way to get through it, with some functions.

    I use an intermediate sheet, and call my data like this:

    If it is a column F with numbers:
    =arrayformula(value(QUERY('Detailed Reports'!F2:F; "select *")))

    If it is a column D with dates (DD/MM/YYYY):
    =arrayformula(IFERROR(DATEVALUE('Detailed Reports'!D2:D)))

    That's about it :-)

  18. ecoevolver
    Member
    Posted 3 years ago #

    Hi there,

    Neat plugin, thanks! Once i get it working i'm hopeful it will help me out!

    When I try the above script solution, it fails. I just generated the script today and have the latest of everything.

    I created a new spreadsheet, added the functions. Pasted the code in with my creds.

    the error is:

    error: Request failed for http://domain.ca/wp-content/plugins/contact-form-7-to-database-extension/export.php?form=Contact+form+1 returned code 404. Server response: <HTML> <HEAD><TITLE>Page Not Found</TITLE></HEAD> <BODY BGCOLOR="#FFFFFF" LINK="maroon" VLINK="maroon" ALINK="maroon"> <CENTER> <TABLE WIDTH="85%" BORDER="1" BORDERCOLOR="#000000" CELLSPACING="0" CELLPADDING="3"> <TR> <TD BGCOLOR="#e5dccd"> <TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%"> <TR> <TD WIDTH="40%"><IMG SRC="http://images.godaddy.com/hosting/hdr_sorry_small.gif" BORDER="1" BORDERCOLOR="#000000"></TD> <TD ALIGN="CENTER"><H2 STYLE="font-family: arial, sans-serif">Page Not Found</H2></TD> </TR> </TABLE>

    Any ideas? The file is there so I'm not sure what the problem is.

    Cheers,

  19. ecoevolver
    Member
    Posted 3 years ago #

    Found the problem.

    Original line produced by your php:
    var url = siteUrl + "/wp-login.php?redirect_to=/wp-content/plugins/contact-form-7-to-database-extension/export.php%3Fform%3D" + encformName;

    Needs to be:
    var url = siteUrl + "/wp-login.php?redirect_to=/subdir/wp-content/plugins/contact-form-7-to-database-extension/export.php%3Fform%3D" + encformName;

    its missing the sub directory of the site.

    Works great now!

  20. ecoevolver
    Member
    Posted 3 years ago #

    Ha, ok last problem I hope!!

    :))

    the field doesn't update. Simply opening the file doesn't fetch new data. I had to copy and paste the function call again to retrieve new data.

    Any ideas how to get the spreadsheet to update by simply re-running the function?

  21. Michael Simpson
    Member
    Plugin Author

    Posted 3 years ago #

    I notice that sometimes when I open mine it updates, sometimes not. I did some research on the web to see if there is a way to force recalculation on google SS. This can be done in Excel by hitting CTRL+ALT+F9 but there is no equivalent in Google SS and no available function to do it either.

    It appears to me that if I edit the formula cell and just add a space at the end then it appears to recalc. Does that work for you?

    The alternative, according to what I have read, is instead of putting a formula in a cell, create a script function to set the data in the spreadsheet in the right cells, and set up a menu option or timer to run that script, which would presumably update the data each time it is run.

  22. Michael Simpson
    Member
    Plugin Author

    Posted 3 years ago #

    Regarding the subdir issue:

    var url = siteUrl + "/wp-login.php?redirect_to=/subdir/wp-content/plugins/contact-form-7-to-database-extension/export.php%3Fform%3D" + encformName;

    Does it work if it were coded:

    var url = siteUrl + "/wp-login.php?redirect_to=" + siteUrl + "/wp-content/plugins/contact-form-7-to-database-extension/export.php%3Fform%3D" + encformName;

    If so, that is an easy fix for me to make.

  23. dclimber
    Member
    Posted 3 years ago #

    Hi thanks for great plugin!

    I have followed instruction for Setting up a Google Spreadsheet to pull in data from WordPress.

    I get this error message? "request took t long to complete and was canceled"

    Any suggestions thanks in advance

  24. Michael Simpson
    Member
    Plugin Author

    Posted 3 years ago #

    That is probably the browser connection timing out before the server replies that the Google Doc upload is done. Try again and see if it completes in time. Also, log into Google Docs and see if the file actually got uploaded.

    I will put something in the next release to tell the browser to have a longer timeout but the browser may ignore it.

  25. dclimber
    Member
    Posted 3 years ago #

    Hi Thanks for the reply
    I have made a spreadsheet in google docs and added the code to cellA2 but the error message "request took t long to complete and was canceled" continues.

    I am unsure what you mean by check google docs. As the spreadsheet is there I made it? its that the spreadsheet is not pulling the database info from wordpress. Woud make this very very effective plugin to be in google as I am not using excel always.

    Thanks for you time and patience

  26. Michael Simpson
    Member
    Plugin Author

    Posted 3 years ago #

    I was confused. Disregard my last message. I thought you were referring to the Google SS (uploading a SS to Google) but you mean Google Live Data.

    I pushed release 1.4.4 today, upgrade and see if that works any better. There doesn't look like a lot I can do if the issue is the Google Docs script UrlFetchApp.fetch() call to get the web page is timing out. There is no way to set its timeout.

  27. Juliete
    Member
    Posted 3 years ago #

    Hi There,
    I am currently using the following formula in Google docs spreadsheets to pull data from google on a list of keywords. I ran into a problem, that being it did not work in regular Excel.

    Here is the formula:
    =getTopResults(b2)

    Can you come up with a solution to make it work in regular excel..?

  28. Michael Simpson
    Member
    Plugin Author

    Posted 3 years ago #

    Sounds like you are saying you have a formula in Google Spreadsheet and you want the same formula in Excel. But I don't understand what this has to do with my plugin....are you on the right forum?

  29. let me see...
    Member
    Posted 3 years ago #

    Hello Michael,
    thank you for the work you put in this plugin.
    Today I tried to do the live export to google using your how-to. However, Google tells me "#ERROR!" and "Fehler beim Exportieren", which translates like "error during export". I checked and interchanged multiple times username and passwort, added one empty space at the end of the line, experimented with the "optional search" tag in the middle, but to no avail.
    Can you help me?

    ///
    Let me add that the export as Excel Internet Query doesn't work for me as well. I allowed Excel to connect als requested, but yet it tells me, that it cannot establish the connection to the URL with the export function in it.

  30. Michael Simpson
    Member
    Plugin Author

    Posted 3 years ago #

    I think both errors are related (Google Live Data and Excel Internet Query). In both cases the spreadsheet (Google or Excel) is trying to automatically login to your site and hit an export URL.

    Some things you might check:

    1. You are logging in with a user/password same as you can use to see the data in the admin panel. If you created a different user login to do this export, go to Database Options and be sure that you have given users of that level (Subscriber, Author...) access to view the data. Try logging in to Admin as that user and see if you can see the form data in the Database page.

    2. Does your WordPress installation start at the root of your website? For example, you get to it using a URL like "http://mysite.com" and not "http://mysite.com/something"

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic