I've written a Google Sheet to SQL automation using WayScript

Hey there DEV.to community!

Recently I've got the chance to know a website called WayScript providing you a really powerful tool to create your own scripts using a visual environment. WayScript is not like other visual tools! It lets you use JavaScript and Python to continue your programming using them if you like.

So I decided to try it out and I wrote a script which converts a sheet from my Google Sheets to a SQL insert statement.

You can access my script here: Google Sheet to SQL

So here is how I've done it. Of course, you need to create an account to create your own script or clone my script.

You should find the New Script button pretty easily up on the menu. After clicking on the New Script button you will be asked to enter a name for your script, then you are in the right place to start writing your script.

Now you can search for the Google Sheet module then drag and drop it in the main function of your script.

After adding the module you will see a setting section on the left panel (you can access this section by clicking on the module in the script tree later) which you have to configure it so it can access the sheet you want, here I used a simple sheet (you can check it out here).

Click on each Import Column so they become available as a variable and can be used later in our program.

Then search for the Create Variable module and add it to your script so your script looks like this so far:

Click on the Create Variable module you just created so the settings section opens up and configure it to add a new variable to your script called table_name.

Next, we are going to dive into programming a little bit deeper using JavaScript as our programming language. WayScript also supports Python and it is only a matter of personal preference and since I like JavaScript more, I decided to write my program using it, you can choose Python if you prefer.

In order to do so add a JavaScript module to your script and edit its code and input the code below in it:

const keys = []
const recordsCount = variables['Column_0'].length - 1
const values = []

for(let i = 0; i < recordsCount; i++) {
    values[i] = []
}

for(const column in variables) {
    if(typeof variables[column] !== 'object') continue
    const thisColumn = variables[column]


    keys.push('\'' + thisColumn[0].toLowerCase().replace(/\s/, '_') + '\'')

    for(let i = 0; i < recordsCount; i++) {
        values[i].push('\'' + thisColumn[i + 1] + '\'')
    }
}

let insertValues = []

for(let i = 0; i < recordsCount; i++) {
    insertValues[i] = '(' + values[i].join(',') + ')'
}

let sqlInsert = 'INSERT INTO ' + variables.table_name + ' (' + keys.join(', ') + ') VALUES ' + insertValues.join(', ') + ';';

console.log(sqlInsert)

As you can see, here we used a variable called variables which is not defined! The variables variable is provided by WayScript itself which contains all the columns we imported and another variable called table_name that we created using the Create Variable module before. The variables variable is an object containing the data added to it from any inputs.

So this JavaScript code simply processes the columns imported from the Google sheet and generates an SQL insert statement, which you can see the output if you run your script.

Your final script tree should look similar to this:

You can run your script by clicking on the Run Main button.

WayScript is way more than only this and you can schedule your script to run at a specific hour during the day and so on.

I hope you enjoyed this and share your experience with WayScript here with me.

Comments

[easrng]

You can query GSheets with SQL and have it output CSV. The catch is, there is no where clause. You use the gid query parameter to specify what sheet to query. the url format is https://docs.google.com/spreadsheets/d/*sheetid*/gviz/tq?tqx=out:csv&tq=*SQL query*&gid=*sheetid*