What do I change in the following code to identify already existing sheet and add data to it corresponding to data added in master sheet?

Question

[1]I have various sheets with the name of my clients in a spreadsheet. And I have one sheet where payments done by them are added using form. Now, I want this code to insert payment details into the clients' respective sheets by matching the sheet name to the name of client in payment details sheet.

Sample worksheet for your reference

var ss=SpreadsheetApp.getActiveSpreadsheet();
var master = ss.getSheetByName('Payment Details');
var sheets = ss.getSheets(); // number of sheets
var colWidth = master.getLastColumn();
function onOpen() {
  var menuEntries = [ {name: "Copy selected Rows to sheets", functionName: "copyRowsOnConditionV2"},];
  ss.addMenu("Copy functions",menuEntries);// custom menu
}
function copyRowsOnConditionV2() {
  var sheetNames = [];// array of existing sheet names
  var sheets = ss.getSheets();// number of sheets
  for(s=0;s<sheets.length;++s){sheetNames.push(sheets[s].getName())};
  ss.getActiveSelection().setBackground('#ffffbb'); 
  var selectedfirstRow = ss.getActiveSelection().getRowIndex();
  var selectedHeigth = ss.getActiveSelection().getHeight()
  var selectedFullRange = master.getRange(selectedfirstRow,1,selectedHeigth,colWidth);
  var data = selectedFullRange.getValues();
  for(n=0;n<data.length;++n){
if(data[n][2].length<60){
  // Check if there is a sheet with this name already (uses names without the space)
  if (sheetNames.indexOf(data[n][2])) { 
    ss.insertSheet(data[n][2]) // Insert a new sheet WITH THE SPACE
    sheetNames.push(data[n][2]) // Add new sheet name to array of sheet names
  }
  var dest = ss.getSheetByName(data[n][2]);//find the destination sheet
  Logger.log(data[n][2])
  // Find the last column with data
  var lastcol = dest.getLastColumn();
  // Set the range to the next col over
  dest.getRange(31, (lastcol +1)).setValue(data[n][3]);
  dest.getRange(32, (lastcol +1)).setValue(data[n][4]);
  var destRange = dest.getRange('h33').setFormula("=h30-h31-i31-j31-k31-l31-m31-n31");
    }
  }
}

[1]: https://i.stack.imgur.com/yGmwk.png [Getting this error when running the code] I want it to add the data to the same sheet instead of creating new one.

You may refer with this thread to update cells after form submission. Range.setValues([Array]) also might be your solution. Note the s in setValues(...). — abielita
@abielita no man! Not what I was looking for. The below code does it for me. works like a charm. — Yogesh Dangi

random-parts random-parts · Accepted Answer · 2017-09-27T14:19:21

Was not completely sure what you wanted with the spaces - or didnt want. Commented out are the lines to use with spaces. Also for the d30,d31,d32 empty column question, you can use getLastColumn() to find the last column with data and add a +1 for the range's column position to move over to the next empty column.

function copyRowsOnConditionV2() {
  var sheetNames = [];// array of existing sheet names
  var sheets = ss.getSheets();// number of sheets
  for(s=0;s<sheets.length;++s){sheetNames.push(sheets[s].getName())};

  ss.getActiveSelection().setBackground('#ffffbb'); 
 ...
  var data = selectedFullRange.getValues();

  for(n=0;n<data.length;++n){
    if(data[n][2].length<60){
      // Check if there is a sheet with this name already (uses names without the space)
      if (sheetNames.indexOf(data[n][2]) == -1) { 

        ss.insertSheet(data[n][2]); // Insert a new sheet WITH THE SPACE
        sheetNames.push(data[n][2]); // Add new sheet name to array of sheet names

        // Apply pending Spreadsheet changes
        SpreadsheetApp.flush();
      }

      var dest = ss.getSheetByName(data[n][2]);//find the destination sheet

      // Find the last column with data
      var lastcol = dest.getLastColumn();

      // Set the range to the next col over
      dest.getRange(30, (lastcol +1)).setValue(data[n][3]);
      dest.getRange(31, (lastcol +1)).setValue(data[n][4]);
      dest.getRange(32, (lastcol +1)).setFormula("=d29-d30-e30-f30");

   ...

    }
  }
}

edit Updated code to reflect that spaces were wanted

What do I change in the following code to identify already existing sheet and add data to it corresponding to data added in master sheet?

1 Answers