CODEWITHSUNDEEP
google-sheetsgoogle-apps-scripttriggersgoogle-workspace
Luan
Luan

Reputation: 21

Onedit update cells use row ID in B2 to locate row line

I'm trying to make a customer profile page to make viewing selected clients easy on the eye and to edit. I want to edit and update on edit the data from the easy on the eye section (cells B3, B5, C3, E4) Which will in turn update the row. From what I understand I can't use a formula for this, it would need to be app script.

I have found on edit update appscript. However for this to work you have to add column and row number. The data is filtered in cell A13. It is then query(to show the information on the easy on the eye section

The ID's in col A corrispond to the row numbers. Could this be used to locate row to update onedit of specific cells in the selected range? The range is filtered (ROW A13 by the ID types into cell B2

Here is the script

function onEdit(e) {

var range = e.range;
var spreadSheet = e.source;
var sheetName =        spreadSheet.getActiveSheet().get  Name();
var column =     range.getColumn();
var row = range.getRow();
var inputValue = e.value;

if(sheetName == 'ONEDIT' &&          column == 1 && row == 1)
{  
    SpreadsheetApp.getActiveSpreadseet().getSheetByName('ONEDIT').getRange('A2').setValue([inputValue]);    
} 
}

How do I edit this appscript to include multiple. Cells based on the row ID in B2

Here is example sheet

https://docs.google.com/spreadsheets/d/1oGxoqIFpHDKt7-_qsR0WZz2JJDKQkEE0j7yiNFLOCQY/edit?usp=drivesdk

Upvotes: 0

Views: 45

Answers (1)

player0
player0

Reputation: 1

try:

function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var sheetName = sheet.getName();
  var row = range.getRow();
  var column = range.getColumn();
  var newValue = e.value;
  var oldValue = e.oldValue;

  // Check if the edit is in the correct sheet and within the specified range
  if (sheetName === 'CRM' && ((row >= 3 && row <= 5 && column >= 2 && column <= 5) || (row === 1 && column === 2))) {
    var b1Value = sheet.getRange('B1').getValue();

    // Check if B1 is true or becomes true
    if ((b1Value === true || b1Value === 'TRUE' || b1Value === 'true' || b1Value === 1) && (oldValue !== true && oldValue !== 'TRUE' && oldValue !== 'true' && oldValue !== 1)) {
      var id = sheet.getRange('B2').getValue();

      // Find the matching row in column A
      var dataRange = sheet.getRange('A15:A').getValues();
      var rowIndex = dataRange.findIndex(function(row) {
        return row[0] === id;
      });

      if (rowIndex !== -1) {
        var targetRow = rowIndex + 15; // Adjust to actual row number

        // Update the row with new values
        var updateValues = {};
        updateValues['B' + targetRow] = sheet.getRange('B3').getValue();
        updateValues['C' + targetRow] = sheet.getRange('C3').getValue();
        updateValues['D' + targetRow] = sheet.getRange('E4').getValue();
        updateValues['E' + targetRow] = sheet.getRange('B5').getValue();

        // Update D column if E4 is not empty
        if (range.getA1Notation() === 'E4' && newValue !== '') {
          updateValues['D' + targetRow] = newValue;
        }

        // Update E column if B5 is edited
        if (range.getA1Notation() === 'B5') {
          updateValues['E' + targetRow] = newValue;
        }

        sheet.getRange(targetRow, 2, 1, Object.keys(updateValues).length).setValues([Object.values(updateValues)]);
      }
    }
  }
}

added checkbox to B1 for convenience, so updating will happen only if checkbox is checked

enter image description here

spreadsheet demo

Upvotes: 0

Related Questions