Great video very easy to follow. I was up until 3AM last night trying to see what I did wrong and found out that it was because I used MySQL 8.0 instead of 5.x. Definitely never not using default settings ever again
Thanks so much. I am learning a lot with your tutorial. Question. in the other way around. Can I populate a Cloud Sql db from a Google_Form ? Currently I am populating a Spreadsheet from Customers entries via an webpage based Google Form, but now my spreadsheet is heavier and heavier and i would need to move to a more reliable db. Thanks
Yes, that should be possible. You can create a bound script within the form itself, capture the form values as their submitted, and use the same Jdbc.getCloudSqlConnection() to add the new values to your database. Once your db connection is setup, you can unlink the spreadsheet. In order to get the values from the form itself, you will need to create an installable trigger, and use something like the script below. (more about installable triggers: developers.google.com/apps-script/guides/triggers/installable#google_apps_triggers) function formSubmitted(e) { var items = e.response.getItemResponses(); for (var i in items){ Logger.log("getItem().getTitle()=%s, getResponse()=%s", items[i].getItem().getTitle(), items[i].getResponse()); } } Hope that helps! -Micah
At 12:56, I cover adding new data to the SQL table. ruclips.net/video/VFE0Cbq581c/видео.html But I do not specifically cover how to get the data from sheets in this video.
I want to run SQL queries on my google sheet files in order to get data insights out of my unsorted data. Is it preferred for me to connect my google sheets to Google cloud? How to do that? If not, Whats the alternative?
If your data is already in Google Sheets, you can use the QUERY() function to run queries on the data. There is no need to connect it to Google Cloud unless your data is slowing down the sheet or you want to store the data in a database.
You can use the properties service, so the login credentials are not stored "out in the open," but a user that knows what they're doing could easily discover the credentials. The only method that I am aware of to make this secure would be to use a WebApp. This would allow users to use the login credentials and the data without being able to "see" what's going on. Note: Giving users edit access to a spreadsheet also gives them edit access to the script.
Absolutely! All you need to do is create a time-based trigger for readFromTable(). Here is a quick video showing how to create a trigger. ruclips.net/video/Ci_-5nXDsSY/видео.html
This is a great tutorial! i'm just wondering how I can connect Cloud SQL to an App engine using python? would you be able to make a video on that? thanks :)
Not sure anyone will answer, but why do I need a temp array in the for loop? Can I not push it in the final array immediately? If declared before the loop, should have the same effect, or not? (Still learning JavaScript (and programming) and pushing stuff into arrays in loops is kind of confusing to me)
Short answer: The temp array is necessary for creating a 2D array that can be output to the sheet in one call. Long answer: The inner FOR loop creates a 1D array of an individual row's values. It then puts that 1D array into the outer WHILE loop's array thereby creating a 2D array. This 2D array is then put into the sheet using setValues. I hope that answers your question.
Hello and thank you so much for your video it helps a lot I have an issue with a connection and I think it’s because There are some new security steps I need to make. Do you know about them? Thanks!
Hello!! Finally found the problem: it was the MySQL version, It seems it should be 5.7, I was Using 8.0 and I do not know the reason but I seems is because in Mysql 8 the credentials configuration may be different. Regards!
I have not tried it myself. The documentation does not seem to make a distinction between MySQL and PostgreSQL, so you should be able to connect to any instance of Cloud SQL using the method shown in the tutorial regardless of the instance type (SQL Server, MySQL, PostgreSQL)
Thanks for your comment, Nick! I would suggest that the title is not misleading, rather it is an issue of semantics based on your usage. Because I create videos for Google Sheets, I want to show people how to connect Google Sheets to Cloud SQL, because they are starting from Google Sheets and going to Cloud SQL (a service they may not be very familiar with). While the connection is being made from Sheets to Cloud SQL in the most technical terms (thereby eliciting your comment that points out that Cloud SQL is the service being connected to and not connected from), the English language provides flexibility for me to communicate my meaning to my specific audience without being misleading. Cheers!
Great video very easy to follow. I was up until 3AM last night trying to see what I did wrong and found out that it was because I used MySQL 8.0 instead of 5.x. Definitely never not using default settings ever again
Thanks for the video! Great step-by-step guide to do exactly what I want
You're welcome!
Awesome video, All problems are solved.
Glad to hear it!
This is awesome; thank you very much for this tutorial. I use a lot of power pivot in MS excel and this is just as good.
Thank you! new sub!
Thanks! Glad you enjoyed the video.
Thanks so much. I am learning a lot with your tutorial. Question. in the other way around. Can I populate a Cloud Sql db from a Google_Form ?
Currently I am populating a Spreadsheet from Customers entries via an webpage based Google Form, but now my spreadsheet is heavier and heavier and i would need to move to a more reliable db. Thanks
Yes, that should be possible. You can create a bound script within the form itself, capture the form values as their submitted, and use the same Jdbc.getCloudSqlConnection() to add the new values to your database. Once your db connection is setup, you can unlink the spreadsheet.
In order to get the values from the form itself, you will need to create an installable trigger, and use something like the script below.
(more about installable triggers: developers.google.com/apps-script/guides/triggers/installable#google_apps_triggers)
function formSubmitted(e) {
var items = e.response.getItemResponses();
for (var i in items){
Logger.log("getItem().getTitle()=%s, getResponse()=%s", items[i].getItem().getTitle(), items[i].getResponse());
}
}
Hope that helps!
-Micah
@@EfficientSmallBusiness Thank you!
This is great! Is it possible to push data from Sheets to SQL though, rather than just reading SQL tables into SHeets?
At 12:56, I cover adding new data to the SQL table. ruclips.net/video/VFE0Cbq581c/видео.html
But I do not specifically cover how to get the data from sheets in this video.
I want to run SQL queries on my google sheet files in order to get data insights out of my unsorted data. Is it preferred for me to connect my google sheets to Google cloud? How to do that? If not, Whats the alternative?
If your data is already in Google Sheets, you can use the QUERY() function to run queries on the data. There is no need to connect it to Google Cloud unless your data is slowing down the sheet or you want to store the data in a database.
Is there a way to mask the user id and password strings to make this more secure? 8:40 time on video.
You can use the properties service, so the login credentials are not stored "out in the open," but a user that knows what they're doing could easily discover the credentials.
The only method that I am aware of to make this secure would be to use a WebApp. This would allow users to use the login credentials and the data without being able to "see" what's going on.
Note: Giving users edit access to a spreadsheet also gives them edit access to the script.
Really interesting tutorial, thanks a lot! Do you know if the refresh of the data can be made automatically, at a given point in time ?
Absolutely! All you need to do is create a time-based trigger for readFromTable(). Here is a quick video showing how to create a trigger. ruclips.net/video/Ci_-5nXDsSY/видео.html
This is a great tutorial! i'm just wondering how I can connect Cloud SQL to an App engine using python? would you be able to make a video on that? thanks :)
is it possible to use postgresql instead of mysql?
It is not currently possible to use postgres with Apps Script
How to do viceversa?
Like enter data inside googlesheet and it db gets updated
Use the function writeManyRecords() to update the database
Not sure anyone will answer, but why do I need a temp array in the for loop? Can I not push it in the final array immediately? If declared before the loop, should have the same effect, or not? (Still learning JavaScript (and programming) and pushing stuff into arrays in loops is kind of confusing to me)
Short answer: The temp array is necessary for creating a 2D array that can be output to the sheet in one call.
Long answer: The inner FOR loop creates a 1D array of an individual row's values. It then puts that 1D array into the outer WHILE loop's array thereby creating a 2D array. This 2D array is then put into the sheet using setValues. I hope that answers your question.
Hello and thank you so much for your video it helps a lot
I have an issue with a connection and I think it’s because There are some new security steps I need to make. Do you know about them? Thanks!
I have not kept up-to-date with Google Cloud SQL, so I am not familiar with any new security steps.
@@EfficientSmallBusiness Thanks! If i am able to do it, count with my reply =D
Hello!! Finally found the problem: it was the MySQL version, It seems it should be 5.7, I was Using 8.0 and I do not know the reason but I seems is because in Mysql 8 the credentials configuration may be different. Regards!
@@jorgeaguayoc Thanks for the update!
Hi, can I use it for Cloud SQL for postgresSQL?
I have not tried it myself. The documentation does not seem to make a distinction between MySQL and PostgreSQL, so you should be able to connect to any instance of Cloud SQL using the method shown in the tutorial regardless of the instance type (SQL Server, MySQL, PostgreSQL)
Authorization Error
Error 401: deleted_client
The OAuth client was deleted.
Shouldn't the name of this video be "Connect Cloud SQL to Google Sheets"? Your title is misleading.
Thanks for your comment, Nick!
I would suggest that the title is not misleading, rather it is an issue of semantics based on your usage. Because I create videos for Google Sheets, I want to show people how to connect Google Sheets to Cloud SQL, because they are starting from Google Sheets and going to Cloud SQL (a service they may not be very familiar with). While the connection is being made from Sheets to Cloud SQL in the most technical terms (thereby eliciting your comment that points out that Cloud SQL is the service being connected to and not connected from), the English language provides flexibility for me to communicate my meaning to my specific audience without being misleading.
Cheers!