Google Apps Scripts - Add specific value to each row being processed in a loop-CodePudding

so I want to write a function that takes leads from a source sheet, and then, sends them to another sheet (the destsheet) if they haven't been sent yet. The difference between a lead that has been sent and a lead that hasn't been is that a lead sent should have "yes" in column K (column 10)

I have managed to get the first part working (sending leads from a sheet to another) but for some reason, I can't get the script to add "yes" when a lead is sent to the other sheet. As you can see below, I've tried adding "values[i][tstCol] = "yes";" in several different spots, but it's not working. Any ideas of what I'm doing wrong? Thanks!

function newLeads() {
  var sourceSheet = SpreadsheetApp.openById('ID').getSheetByName('SHEETNAME');
  var destSheet = SpreadsheetApp.openById('ID').getSheetByName('SHEETNAME');

var values = sourceSheet.getDataRange().getDisplayValues();
var finalValues=[];

var columns = String("0,1,2,3,4,5,6,7,8").split(','); //only want these columns
var tstCol = 10; 
for (var i=0;i<values.length;i  ) {
    var newValues=[];
    //values[i][tstCol] = "yes";
    if (values[i][tstCol] != "yes") {
    //values[i][tstCol] = "yes";
      for (var j=0;j<columns.length;j  ) {
      //values[i][tstCol] = "yes";
        newValues.push(values[i][columns[j]]);
      }
      finalValues.push(newValues);
    }
  }
  destSheet.getRange(destSheet.getLastRow()   1, 1, finalValues.length, finalValues[0].length).setValues(finalValues);
}

CodePudding user response：

Take a look at this.

function newLeads() {
  try {
    var sourceSheet = SpreadsheetApp.openById('ID').getSheetByName('SHEETNAME');
    var destSheet = SpreadsheetApp.openById('ID').getSheetByName('SHEETNAME');

    var values = sourceSheet.getDataRange().getDisplayValues();
    var finalValues=[];

    var columns = String("0,1,2,3,4,5,6,7,8").split(','); //only want these columns
    var tstCol = 10; 
    for (var i=0;i<values.length;i  ) {
      var newValues=[];
      if (values[i][tstCol] != "yes") {
        values[i][tstCol] = "yes";  // is this where you want to set to yes
        for (var j=0;j<columns.length;j  ) {
          newValues.push(values[i][columns[j]]);
        }
        finalValues.push(newValues);
      }
      values[i] = [values[i][tstCol]];  // this would extract column tstCol
    }
    destSheet.getRange(destSheet.getLastRow()   1, 1, finalValues.length, finalValues[0].length).setValues(finalValues);
    sourceSheet.getRange(1,tstCol 1,values.length,1).setValues(values);
  }
  catch(err) {
    console.log(err);
  }
}