CODEWITHSUNDEEP
mysqlexcelgoogle-sheets
milenmk
milenmk

Reputation: 544

Set starting row and column when updating google sheet from mysql with jdbc

I have this code to auto insert/update data in Google Sheet from MySql database.

var server = 'server';
var port = 3306;
var dbName = 'db_name';
var username = 'db_username';
var password = 'db_pass';
var url = 'jdbc:mysql://'+server+':'+port+'/'+dbName;

function readData() {
 var conn = Jdbc.getConnection(url, username, password);
 var stmt = conn.createStatement();
 var results = stmt.executeQuery('SELECT .... ');
 var metaData=results.getMetaData();
 var numCols = metaData.getColumnCount();
 var spreadsheet = SpreadsheetApp.getActive();
 var sheet = spreadsheet.getSheetByName('Sheet1');
 sheet.clearContents();
 var arr=[];

 for (var col = 0; col < numCols; col++) {
   arr.push(metaData.getColumnName(col + 1));
 }

 sheet.appendRow(arr);

while (results.next()) {
 arr=[];
 for (var col = 0; col < numCols; col++) {
   arr.push(results.getString(col + 1));
 }
 sheet.appendRow(arr);
}

results.close();
stmt.close();
sheet.autoResizeColumns(1, numCols+1);
}

How can I set starting row and column for the data inserted? Now it starts from 'A1' and I'd like to start from 'D5'?

Tried to add var cell = sheet.getRange('A1'); after var sheet = with no result.

Upvotes: 0

Views: 95

Answers (2)

Mart&#237;
Mart&#237;

Reputation: 2861

The basic idea would be to split the problem into 2:

  1. Get the data from your your MySQL into a 2D array
  2. Add the data to the spreadsheet.

function readData() {
  // Get data
  const conn = Jdbc.getConnection(url, username, password)
  const stmt = conn.createStatement()
  const results = stmt.executeQuery('SELECT .... ')
  
  const columnNames = getColumnNames(results)
  const data = getStringData(results)
  
  results.close()
  stmt.close()
  
  // Set data to spreadsheet
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = spreadsheet.getSheetByName('Sheet1')
  sheet.clearContents()
  
  addToRange(sheet.getRange('B5'), [
    columnNames,
    ...data,
  ])
}


function getColumnNames(results) {
  const result = []
  const metaData = results.getMetaData()
  const numCols = metaData.getColumnCount()
  
  for (let col = 0; col < numCols; col++) {
    result.push(metaData.getColumnName(col + 1))
  }
  
  return result;
}

function getStringData(results) {
  const result = [];
  while (results.next()) {
    const row = [];
    for (let col = 0; col < numCols; col++) {
      row.push(results.getString(col + 1))
    }
    result.push(row)
  }
  return result
}

function addToRange(range, values) {
  return range
    .offset(0, 0, values.length, values[0].length)
    .setValues(values)
}

Adding function also make the code more readable and help to split the problem.

Note that I'm adding the column headers to the row 5 (I join the data and the headers). You can set them separately in different ranges if you so desire.

References

Upvotes: 1

Rocky
Rocky

Reputation: 1000

try below code:

var server = 'server';
var port = 3306;
var dbName = 'db_name';
var username = 'db_username';
var password = 'db_pass';
var url = 'jdbc:mysql://'+server+':'+port+'/'+dbName;

function readData() {
 var conn = Jdbc.getConnection(url, username, password);
 var stmt = conn.createStatement();
 var results = stmt.executeQuery('SELECT .... ');
 var metaData=results.getMetaData();
 var numCols = metaData.getColumnCount();
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = spreadsheet.getSheetByName('Sheet1');
 sheet.clearContents();
 var arr=[];

 for (var col = 0; col < numCols; col++) {
   arr.push(metaData.getColumnName(col + 1));
 }

 sheet.appendRow(arr);

while (results.next()) {
 arr=[];
 for (var col = 0; col < numCols; col++) {
   arr.push(results.getString(col + 1));
 }
 
     //sheet.appendRow(arr);
     
     var range = sheet.getRange(5,4, arr.length, arr[0].length);
     range.setValues(arr);  

}

results.close();
stmt.close();
sheet.autoResizeColumns(1, numCols+1);
}

Upvotes: 0

Related Questions