Google sheets- loop search column, select tow

Question

I have a google spreadsheet that i have one last problem i cant seem to solve.

i added a button to this script, and when i press the button it triggers the AddClient function.

How can i make the script below loop down all rows in column 3 searching for the yes value, when it finds it, copy the row below it to sheet "client" and then stop?

function AddClient(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "SETUP" && r.getColumn() == 3 && r.getValue() == "yes")     {
    var row = r.getRow() + 1; // Add 1 to the active row
    var targetSheet = ss.getSheetByName("client");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 2, 1, 4).copyTo(target,  {contentsOnly:true}); //Only selecting column 2, then the following 4 columns
  }
}

Edit* Example document: https://docs.google.com/spreadsheets/d/1DFbAp0IN8_UFv9u8kWiZBTxDogj4e7FnuAPzC0grDw0/edit?usp=sharing

Any help greatly appreciated!

Cheers, Reiel

What's the interaction you are trying to design? someone enters yes into column three and the row is copied to another sheet? — Robin Gertenbach
sorry, now I'm even less in the clear, do you have one button and the script shall copy everything with a yes or is there always a yes everywhere and you have a button for every row? — Robin Gertenbach
Maybe a screenshot or even better a sample spreadsheet would help — Robin Gertenbach
Sorry about not clearing up anything :P i edited again, added example document that is exactly the same as mine but difrent data names. — Latent-code

Robin Gertenbach Robin Gertenbach · Accepted Answer · 2017-04-04T15:04:20

Since you have a static form the position of the informatin to be copied will not change
Since we know we want to copy over the data we won't need to do any validation of where we are so all of that stuff can go
Sheets have an appendRow method that take care of the bookkeeping involved with finding the last row

This allows us to simplify the script to the following:

function AddClient() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = ss.getSheetByName("SETUP").getRange(25, 2, 1, 4).getValues()[0];
  ss.getSheetByName("client").appendRow(data);
}

Edit: To remove duplicates you could do the following:

function AddClient() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = ss.getSheetByName("SETUP").getRange(25, 2, 1, 4).getValues()[0];
  var clients = ss.getSheetByName("client").getDataRange().getValues();

  if (!clients.filter(function(row) {return data.join("|") === row.join("|");})) {
    ss.getSheetByName("client").appendRow(data);
  }  
}

Note that for the particular example there are some problems because the leading zero gets cut off. Sheets is a bit weird and sometimes tries to force the format to be a number even when you set the cells' formats to Text...

Google sheets- loop search column, select tow

1 Answers