I need a function to allow me to update the sheet, for example, if I want to update one row, I want to be able to do it without erasing all the values in that row

//function to get data by email -  so as to get row with corresponding email
function getDataByEmail1(email){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataCells = sheet.getDataRange().getValues();
  var result = null;
  for(var i = 1; i < dataCells.length; i  ){
    if(dataCells[i][0] == email){
      result = dataCells[i];
      break;
    }
  }
  return result;
}

// this code will check mySheet using the above function getDataByEmail(), and if an email exists, it will proceed to update that record... if email doesn't exit, it will execute the else if statement, which will create new record instead of updating.

function updateRecord(email, password, id, sex, date) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var mySheet = sheet.getSheetByName("sheet1");  
  var getLastRow = mySheet.getLastRow();
  var check = getDataByEmail(email);
  var table_values = mySheet.getRange(2, 1, getLastRow - 1, 8).getValues();
  for(i = 0; i < table_values.length; i  ){
    if(table_values[i][0] == email) {
      mySheet.getRange(i 2, 1).setValue(email);
      mySheet.getRange(i 2, 2).setValue(password);
      mySheet.getRange(i 2, 3).setValue(id);
      mySheet.getRange(i 2, 4).setValue(sex);
      mySheet.getRange(i 2, 5).setValue(date);
 
      status = 'Record Updated';
    }    
    else if (check == null){ // this else if statement works, it submits new data to the spreadsheet
      var sheet = SpreadsheetApp.getActiveSpreadsheet();
      var mySheet = sheet.getSheetByName("sheet1"); 
      var data = [[email,password,id,sex,date]];
      var row = mySheet.getLastRow()   1;
      var cel = 1;
      var rowLength = data.length;
      var celLength = data[0].length;
      mySheet.getRange(row, cel, rowLength, celLength).setValues(data);

    status = 'New Record created';
    }      
  }
    return status;
} 


// below is the function i use to test updateRecord()

function updateRecordTest(){
  var data = updateRecord("[email protected]", "pass1", "id1", "id1");
  Logger.log(JSON.stringify(data));
}

// The problem is when I update, it is able to get getDataByEmail, and then updating the row that has the corresponding email.. but if all the columns are not filled out, it erases the values in columns that are not filled out and then submits those new updated records...

I did all kinds of Google Apps Script documentation search, nothing worked for me.

CodePudding user response：

I believe your goal is as follows.

• You want to search an email from column "A" of "sheet1". And, when the email is found, you want to update the row. When the email is not found, you want to append the value to the sheet.
• I guessed that from your script, each email address is a unique value in the column "A".

When I saw your script, when check is null, the rows with the length of table_values are appended. And, I thought that status might be required to be declared. In this case, how about the following modification?

Modified script:

function updateRecord(email, password, id, sex, date) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var mySheet = sheet.getSheetByName("sheet1");
  var getLastRow = mySheet.getLastRow();

  // I modified the below sscript.
  var ar = [email, password, id, sex, date];
  var range = mySheet.getRange(2, 1, getLastRow - 1);
  var check = range.createTextFinder(email).findNext();
  var status;
  if (check) {
    check.offset(0, 0, 1, ar.length).setValues([ar]);
    status = 'Record Updated';
  } else {
    range.offset(getLastRow - 1, 0, 1, ar.length).setValues([ar]);
    status = 'New Record created';
  }
  return status;
}

function updateRecordTest() {
  var data = updateRecord("[email protected]", "pass1", "id1", "id1");
  Logger.log(JSON.stringify(data));
}

• In this modification, the email is searched using TextFinder. So, getDataByEmail(email) is not used.
• When email is found in column "A", the searched row is updated. When email is not found in column "A", the row is appended.

Reference:

• Class TextFinder

CodePudding user response：

Update without deleeting:

function updateRecord(email, password, id, sex, date) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName("sheet1");
  var check = getDataByEmail(email);
  var vs1 = sh1.getRange(2, 1, getLastRow - 1, 8).getValues();
  vs1.forEach(([a, b, c, d, e, f, g, h], i) => {
    if (a == email) {
      sh1.getRange(i   2, 1, 8).setValues([[a, password, id, sex, date, f, g, h]])
      status = 'Record Updated';
    } else if (check == null) {
      sh1.getRange(sh1.getLastRow(), 1, 1, 8).setValues([[email, password, id, sex, date, f, g, h]]);
      status = 'New Record created';
    }
  })
  return status;
}