[Plugin: Contact Form 7 to Database Extension] Pull data in Google docs spreadsheet

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("https://<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!

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.

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 https://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. “https://www.mywordpress.com”
formName: name of the form
user: your login name on your wordpress site
pwd: password

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

It looks like there’s a bug that stops the code from working with wordpress installs hosted in sub-directories. (e.g. https://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.

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

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?

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.

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)

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!

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)

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

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.

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

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