0

I tried to sync my data in MariaDB with my google sheet and I got successful sync, but each time when the trigger happens the existing data is being replaced and the data is being copied as a new one, is there any way that to avoid the deletion of existing data in the google sheets, so that when a new data is recorded the new one alone must be sync to google sheets, Please help me with this. The below code is the google app script code used to trigger the sync

var server = '11.11.11.11';
var port = 3306;
var dbName = 'dummy';
var username = 'username';
var password = 'password';
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 * FROM dashboard_dummy');
    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);
}
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • 1
    I'm by now means an expert in Google Apps Script but since it is mainly JavaScript, consider my side note: always use `let` instead of `var` (see: https://stackoverflow.com/a/11444416) – leun4m Sep 14 '20 at 13:55
  • Isn't there a sequential id in ``dashboard_dummy`` where you can use `WHERE id>lastId`? – TheMaster Sep 14 '20 at 16:30
  • So, your intention is to just append new data to the Spreadsheet when the trigger is executed instead of updating all the data? What do you mean by ```the data is being copied as a new one```? – Mateo Randwolf Sep 15 '20 at 08:47
  • Yes, if the sheet has 20 rows then the data must fill from 21 st row –  Sep 15 '20 at 12:06
  • What about as a workaround to get the new data from your database, then compare it to your actual data in your spreadhseet and find the rows that are new in the database and then just append these as new rows in your Spreadsheet? Would that workaround fit your intentions? – Mateo Randwolf Sep 17 '20 at 09:23

1 Answers1

0

First I got the last row and saved it, then by using a for loop and with the saved last row I used loop to leave the values already present and get the remaining values.