CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
lazyezy
lazyezy

Reputation: 23

Google Script: How to find and update a whole row

I was looking for a way to test if the range is already present. If it's a new entry, it will be added as the last row, if it's not it will update the existing row.

function Submit() {

 var ss =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Placeholder'); 

 var range = ss.getRange('A2:AS2'); 

/*
The string to Test is if C2 from place holder sheet is
already present on the Master Sheet at C column 
*/

 var data = range.getValues();

 var ts =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master'); 

 ts.getRange(ts.getLastRow() + 1, 1, 1, 45).setValues(data); //you will need to define the size of the copied data see getRange()

 Clearfunc();

 SpreadsheetApp.getUi().alert('Submitted');

}

Upvotes: 1

Views: 1440

Answers (2)

lazyezy
lazyezy

Reputation: 23

manage to figure out the answer

function Update(){
  var ss =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Placeholder'); 
  var range = ss.getRange('A2:AS2')
  var data = range.getValues();
  var Key = ss.getRange('C2').getValue(); 
  var ts =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master'); 
  var datarange = ts.getRange('C1:C');
  var datavalues = datarange.getValues(); 

  for (var r=0; r<=datavalues.length; r++) 
  { 
     var ss =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Placeholder'); 
     var Key = ss.getRange('C2').getValue();    
    if(datavalues[r] == Key)
       {ts.getRange(r+1, 1,1,45).setValues(data);}
  }
Clearlookupfunc();
SpreadsheetApp.getUi().alert('Update Submitted');
}

Upvotes: 1

Niek Waarbroek
Niek Waarbroek

Reputation: 160

You can read the values of the master sheet and iterate over the values to determine which row (index) contains the same value. Once you have the index you can update that range with the updated data.

It should look something like this:

// get values
var masterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master');
var masterData = masterSheet.getDataRange().getValues();
var updateIndex;

// loop through master data to find the correct index
masterData.some(function (row, idx) {
  // check column c equals new data column c
  // data[0] because is multidimensional
  if(row[2] === data[0][2]) {
    updateIndex = idx;
    return updateIndex;
  }
});

// in case updateIndex is set we should update the data else it is a new record
if(updateIndex !== undefined) {
  masterSheet.getRange(updateIndex, 1, 1, data[0].length).setValues(data);
}

Upvotes: 0

Related Questions