How to add from mysql to spreadsheet with app script-CodePudding

i want to add data from mysql to spreadsheet using google app script. And the data has been successfully pulled and successfully displayed in the console log, but how do I enter it into the google sheet???

here's the script:

var server = 'xxx.xxx.xxx.xxx';
var port = 3306;
var dbName = 'xxx';
var username = 'xxx';
var password = 'xxxxx';
var url = 'jdbc:mysql://' server ':' port '/' dbName;
 
function readFromTable() {
  try {
    const conn = Jdbc.getConnection(url, username, password);
    const start = new Date();
    const stmt = conn.createStatement();
    stmt.setMaxRows(1000);
    const results = stmt.executeQuery('SELECT * FROM tickets');
    const numCols = results.getMetaData().getColumnCount();

    while (results.next()) {
      let rowString = '';
      for (let col = 0; col < numCols; col  ) {
        rowString  = results.getString(col   1)   '\t';
      }
      Logger.log(rowString); 
      **//here should be a script to input data into google sheet**
    }

    results.close();
    stmt.close();

    const end = new Date();
    Logger.log('Time elapsed: %sms', end - start);
  } catch {
    // TODO(developer) - Handle exception from the API
    Logger.log('Failed with an error %s', err.message);
  }
}

CodePudding user response：

Something like this:

function readFromTable() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  try {
    const conn = Jdbc.getConnection(url, username, password);
    const start = new Date();
    const stmt = conn.createStatement();
    stmt.setMaxRows(1000);
    const results = stmt.executeQuery('SELECT * FROM tickets');
    const numCols = results.getMetaData().getColumnCount();
    let a = [];
    while (results.next()) {
      let row = [];
      for (let col = 0; col < numCols; col  ) {
        row.push(results.getString(col   1));
      }
      a.push(row);
      
    }
    sh.getRange(1,1,a.length,a[0].length).setValues(a);
    results.close();
    stmt.close();

    const end = new Date();
    Logger.log('Time elapsed: %sms', end - start);
  } catch {
    // TODO(developer) - Handle exception from the API
    Logger.log('Failed with an error %s', err.message);
  }
}