• Google Scripts
  • How To Connect Your Webshop To JustReact Through MySQL

How To Connect Your Webshop To JustReact Through MySQL

So you got the information from Google Analytics, but are still missing some critical information to make the right decisions?

Your gut says you must have done more sales, but it's not showing in your Google Analytics dashboard?

Well, than it's time to connect your website or webshop directly to your JustReact dashboard through MySQL!

In about 5 minutes, you will be able to connect to your website and get the information you need.

Just follow these easy steps.

1. Get Your Free JustReact Copy

Get Your Free Justreact CopyGet Your Free Justreact Copy

To get started with JustReact, you first need to get a copy of the JustReact Google Sheet.

Make sure you are logged in with your Google Account and click the button below.

Get Your FREE JustReact Copy


Make your own JustReact CopyMake your own JustReact Copy
Click the Make a copy button to create your own JustReact copy.

The JustReact copy is now available in your account
The JustReact copy is done and a new Menu appears in the top menu.

2. Authorize the JustReact Library

Authorize the JustReact LibraryAuthorize the JustReact Library
Click Help Me option in the JustReact menu to authorize the JustReact Library

Justreact AuthorizeJustreact Authorize
Click the Continue button to authorize the JustReact Library.

Justreact Authorize AccountJustreact Authorize Account
Select the account you want to use with JustReact

Justreact Authorize Allow AccessJustreact Authorize Allow Access
Allow JustReact access by clicking the Allow button.

3. Setup Your remote MySQL Connection

Allow Remote MySQL Connections

To be able to get data from your website through MySQL, you need to setup remote MySQL at your hosting provider.

So ask your hosting provider for remote MySQL support.

And if they don't, you should think about moving your website :-)


Whitelist Google IP RangesWhitelist Google IP Ranges

Go to https://developers.google.com/apps-script/guides/jdbc#other_databases for more information on remote mysql and to see the Google IP ranges.

4. Get Data from your Webshop (VirtueMart)

Setup Mysql Connection SettingsSetup Mysql Connection Settings

In the Google Script Editor, search for MySQL Connection Settings.

Now fill in the variables at function db1_().

If you need to use multiple databases in your project, you can just copy the entire function and rename it to db2_, db3_, etc.


Google Script Mysql Select QueryGoogle Script Mysql Select Query

Now that you entered the connection settings, you need to make a SELECT Query for your data base.

I made an example for VirtueMart, but we'll keep adding query examples from now on.

If you have a great idea for a query, just shoot me a message. If we like it, we'll put it in the new library updates and ofcourse give you credit.


Google Script Mysql FunctionsGoogle Script Mysql Functions

Now go to the Code.gs tab and search for VirtueMart Analytics.

There you'll find the functions for getting sales info from VirtueMart.

The function are pretty straight forward, so go ahead and change them as you like.


Menu Get Vm AnalyticsMenu Get Vm Analytics

Now just click the Get VM Analytics from the JustReact menu, and your dashboard will be updated with the information from VirtueMart.

5. How to get your Google Sheet graphs by email

Open Google Script EditorOpen Google Script Editor

Open the Script editor.


Email GraphsEmail Graphs

Edit the emailGraphs() function. Choose the source sheet for the Graphs: eSet.sheetName = "Dashboard" and change the emails you want to send the graphs to: eSet.emails = "info@xxxxxx"


Email Graphs MenuEmail Graphs Menu

To send the graphs to your email, click the Email Graphs option in the JustReact menu.


Graphs EmailGraphs Email

The "Graphs" email contains the graph images from the source sheet and also the code you can use in your report email.

The graph codes look like {G:0, H:600, W:1200}, where G is the graph number (starting at 0), H is the height of the image in pixels and W is the width in pixels

6. How to email your Google Analytics report

Open Google Script EditorOpen Google Script Editor

Open the Script editor.


Email Report FunctionEmail Report Function

The emailReport() function contains the parameters for sending the Google Analytics report by email.

  • pdfSheet =>The PDF report for the email
  • reportSheet: The XLS report for the email
  • graphSheet: The graphs for the email
  • emailSheet: The HTML code for the email
  • emails: Fill in the emails (comma separated) that should receive the report
  • var d = "YY-MM-DD hh:mm:ss"
    Date/time format for the email subject
  • emailSubject: Email subject
if (onUpdate_("Dashboard", "F19")==1) { emailReport_(eSet); } => This means that the email will only be sent if cell F19 on sheet "Dashboard" has changed since last time. If you want to send it every time, change it to: emailReport_(eSet);

Email Report SheetEmail Report Sheet

This sheet contains the HTML code for the email.

The HTML is build from the top left to the bottom right.

So A1, A2, A3, ......E1000, F1000, etc.

If you want to add a graph, use the {G0; H:600; W:1200} format.

To add a link to the PDF or XLS report, you can use [PDF] or [XLS]


Email Report MenuEmail Report Menu

If your email is ready, you can click the Email Report option in the JustReact menu, and the report will be in your inbox within a few seconds.


Email Report

Now you received the Google Analytics report by email, you can use the info to improve you site or webshop.

No need to spend hours on future reports. You JustReact :-)

7. How to schedule your Google scripts

Open Google Script EditorOpen Google Script Editor

Open the Script editor.


Project TriggersProject Triggers

Click the clock button to open the script triggers screen.


Add Project TriggerAdd Project Trigger

Click the Add Trigger button to add a trigger to the project.


Add Trigger Justreact LibraryAdd Trigger Justreact Library
  1. Select the function you want to run
  2. Select Time-driven as event source
  3. Select the time type for the trigger
  4. Select the interval

You now have a Google Analytics reporting system on autopilot!

Get A FREE Quote Now

Need help setting up your Google script?
Or do you want us to handle your script?

Get in touch with us below and
we'll get back to you asap.


Support

Please type your full name.
Invalid email address.
Invalid Input
Invalid Input
© 2018 JustReact - The Nederlands