CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
DSchrute
DSchrute

Reputation: 29

Google script to find and update rows in other sheets by unique ID

I have about 4 separate spreadsheets that accepts user input and when the user is finished they push a button to ‘log’ the information on another sheet. All of the spreadsheet entries from user input have a unique ID assigned to them.

What I am trying to do is when the user clicks the button the script searches another sheet for the matching ID and updates Column B to the new value.

Basically like a simple lookup, but instead of returning the value, it updates. 

Sheet 1                 Sheet 2
    A       B               A       B 
1  ID123 | Received  >   1  ID123 | Ordered
2                        2

So as the user changes ID123 in spreadsheet 1 to Received, the button searches for ID123 in spreadsheet 2 and if it exists (it always will), cell b1 in sheet 2 gets updated with the value in B1 in sheet 1.

I did come across another post talking about a CRUD web app, but I am thinking that is a bit more complex than what I am looking to do here!

Thank you for any assistance!

EDIT: The button I mentioned is added in via a custom image in which this script will be assigned to.

It might help to add that the 'Starting sheet' will only ever have 1 ID on it. It is the 'destination sheet' that will have multiple ID's, even the same ID repeating multiple times, in which case all matching IDs would need to be updated.

Sample of Starting Sheet and Sample of Destination Sheet

As you can see above, in the 'Starting Sheet', ID3 has a Status of "Received". Upon pushing that Submit button, the script would need to find "ID3" in the 'Destination Sheet' and replace whatever text is in the Value column with "Received". So in this example, ID3 is being updated from "Ordered" to "Received".

Upvotes: 2

Views: 3435

Answers (2)

Cooper
Cooper

Reputation: 64140

try this:

function onEdit(e) {
  if(e.range.getSheet().getName() != 'Sheet1'){return;}
  if(e.range.columnStart==2) {
    var id=e.range.offset(0,-1).getValue();
    var rg=e.source.getSheetByName('Sheet2').getDataRange()
    var vA=rg.getValues();
    for(var i=1;i<vA.length;i++) {
      if(vA[i][0]==id) {
        vA[i][1]=e.value;
        break;
      }
    }    
  }
  rg.setValues(vA);
}

Sheet1 and Sheet2:

enter image description here

You can attach this function to a button as desired:

function updateEntrees() {
  var ss=SpreadsheetApp.getActive();
  var sh1=ss.getSheetByName('Sheet1');
  var rg1a=sh1.getRange(2,1,sh1.getLastRow()-1,1);
  var vA1a=rg1a.getValues();
  var rg1b=sh1.getRange(2,2,sh1.getLastRow()-1,1);
  var vA1b=rg1b.getValues();
  var sh2=ss.getSheetByName('Sheet2');
  var rg2a=sh2.getRange(2,1,sh2.getLastRow()-1,1);
  var vA2a=rg2a.getValues();
  var rg2b=sh2.getRange(2,2,sh2.getLastRow()-1,1);
  var vA2b=rg2b.getValues();
  for(var i=0;i<vA1a.length;i++) {
    for(var j=0;j<vA2a.length;j++) {
      if(vA1a[i][0]==vA2a[j][0]) {
        vA2b[j][0]=vA1b[i][0]
      }
    }
  }
  rg2b.setValues(vA2b);
}

Upvotes: 1

ra89fi
ra89fi

Reputation: 1245

You can copy/paste below code inside existing button function to copy current cell value to destination sheet cell accordingly after search by id. Check for duplicate variable names though.

var acRange = SpreadsheetApp.getActiveSheet().getActiveRange();
var val = acRange.getValue();
var row = acRange.getRow();
var col = acRange.getColumn();
var id = acRange
  .getSheet()
  .getRange(row, 1)
  .getValue();
var rg = SpreadsheetApp.getSheetByName('destination sheet name here').getDataRange();
var vA = rg.getValues();
for (var i = 1; i < vA.length; i++) {
  if (vA[i][0] == id) {
    vA[i][col - 1] = val;
    break;
  }
}
rg.setValues(vA);

Upvotes: 0

Related Questions