Saving data from Google. Sheets to MySQL table using Apps Script.
Prerequisites
- create a table in MySQL database
- A google sheet with data
MySQL table
CREATE TABLE `invoice` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`headquarter` VARCHAR(90) NULL DEFAULT NULL,
`invoice_month` VARCHAR(90) NULL DEFAULT NULL,
`invoice_year` CHAR(4) NULL DEFAULT NULL,
`customer` CHAR(100) NULL DEFAULT NULL,
`invoice_no` VARCHAR(12) NULL DEFAULT NULL,
`invoice_value` VARCHAR(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
;
Apps Script to insert data into MySQL
function googleSheetsToMySQL() {
var sheetName = 'Base Data';
var server = 'database host name';
var port = 15410;
var dbTableName = 'invoice'
var dbName = 'database';
var username = 'user';
var password = 'password';
var url = 'jdbc:mysql://'+server+':'+port+'/'+dbName;
sql = "INSERT INTO " + dbTableName + " (headquarter, invoice_month, invoice_year, customer, invoice_no, invoice_value) VALUES (?, ?, ?, ?, ?, ?);";
var maxRecordsPerBatch = 1000;
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheetName);
var sheetData = sheet.getDataRange().getValues();
var dbConnection = Jdbc.getConnection(url, username, password);
// The following only needs to be set when you are changing the statement that needs to be prepared
// or when you need to reset the variable.
//
// For example, if you were to switch to a different sheet which may have different values, columns,
// structure, and/or target database table.
var dbStatement = dbConnection.prepareCall(sql);
var headquarter;
var invoice_month;
var invoice_year;
var customer;
var invoice_no;
var invoice_value
var recordCounter = 0;
var lastRow;
dbConnection.setAutoCommit(false);
for (var i = 9; i < sheetData.length; i++) {
lastRow = (i + 1 == sheetData.length ? true : false);
headquarter = sheetData[i][1];
invoice_month = sheetData[i][2];
invoice_year = sheetData[i][3];
customer = sheetData[i][4];
invoice_no = sheetData[i][5];
invoice_value = sheetData[i][6];
dbStatement.setString(1, headquarter);
dbStatement.setString(2, invoice_month);
dbStatement.setString(3, invoice_year);
dbStatement.setString(4, customer);
dbStatement.setString(5, invoice_no);
dbStatement.setString(6, invoice_value);
// This command takes what has been set above and adds the request to the array that will be sent
// to the database for processing.
dbStatement.addBatch();
recordCounter += 1;
if (recordCounter == maxRecordsPerBatch || lastRow)
{
try {
dbStatement.executeBatch();
}
catch(e)
{
console.log('Attempted to update TABLE `' + dbTableName + '` in DB `' + dbName + '`, but the following error was returned: ' + e)
}
if (!lastRow)
{ // Reset vars
dbStatement = dbConnection.prepareCall( sql ); // Better to reset this variable to avoid any potential "No operations allowed after statement closed" errors
recordCounter = 0;
}
}
}
dbConnection.commit();
dbConnection.close();
}