Tag: Google sheets

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();
}

Data in Google Sheets