Update a MySQL row depending on the ID in Google Sheets Apps Script

Question

I'm looking to update rows of a MySQL database from a Google Sheet. My current workflow is to import the existing table into one sheet in Google Sheets, copy it to another one where I make changes, then run a new script to update the table.

I could probably do this more efficiently but doing it this way means that I can't do anything automatically and risk breaking my database.

I have created a script where I only update one column and it works except that in my database the id column is not sequential as records have been deleted (eg it starts with row 3 then 5,6,7,9 etc). But my script doesn't account for that and will just use the Google Sheets Row ID to match it (eg in MySQL the first row is row 3, but in Google Sheets it'll be row 1). This means there's a mismatch.

My script is below and I've been tinkering but can't seem to make it work. The Unique IDs are in Column A.

The other thing is that I don't think my loop is efficient as the database will grow beyond 400 rows.

function UpdateDB(e) {

 
  ss = SpreadsheetApp.getActiveSpreadsheet();
  sheet = ss.getSheetByName("writeSheet");
  hookn = sheet.getRange("M2:M300").getDisplayValues() ;
  uid = sheet.getRange("A2:A300").getDisplayValues() ;
  
  server = "1******";
  port = '3306';
  dbName =  "*****";
  username = "*******";
  password = "*********";
  url = "jdbc:mysql://" + server + ":" + port + "/" + dbName + "?characterEncoding=UTF-8";

  conn = Jdbc.getConnection(url, username, password);
  stmt = conn.createStatement();

  for (i=0 ; i<400 ; i++ ) {  
    stmt.execute("UPDATE wp_tripetto_forms SET hooks ='" + hookn[i] + "' WHERE id = " + (i+1) + " ;");
  }


  conn.close();
}

While the rows do update, the IDs are not correct due to the script using the Google Sheet row ID as the MySQL unique ID.

Answer 1

This is how I got it to work:

function UpdateDB(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("writeSheet");
  const hookn = sheet.getRange("M2:M" + sheet.getLastRow()).getDisplayValues().flat();
  const uid = sheet.getRange("A2:A" + sheet.getLastRow()).getDisplayValues().flat();

  const server = "1******";
  const port = '3306';
  const dbName = "*****";
  const username = "*******";
  const password = "*********";
  const url = "jdbc:mysql://" + server + ":" + port + "/" + dbName + "?characterEncoding=UTF-8";

  const conn = Jdbc.getConnection(url, username, password);
  const stmt = conn.createStatement();

  // Retrieve all IDs from the MySQL table
  const resultSet = stmt.executeQuery("SELECT id FROM wp_tripetto_forms");
  const ids = [];
  while (resultSet.next()) {
    ids.push(resultSet.getInt("id"));
  }

  // Update the rows using the retrieved IDs
  for (let i = 0; i < ids.length; i++) {
    const id = ids[i];
    const idx = uid.indexOf(id.toString());
    if (idx !== -1) {
      stmt.execute(`UPDATE wp_tripetto_forms SET hooks = '${hookn[idx]}' WHERE id = ${id};`);
    }
  }

  conn.close();
}