CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
monicab
monicab

Reputation: 45

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

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);
}

Upvotes: 0

Views: 1844

Answers (1)

TheWizEd
TheWizEd

Reputation: 8598

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);
  }
}

Explanation:

The try block is used to see if an error occurs during the block between try and catch; if an error occurs, the catch(err) block is executed and the error is being displayed.

The values array is an array of rows [[1,2,3,4],[5,6,7,8],...] which I converted it to an array [[1],[2],[3]...] of a single column of the spreadsheet type in order to use the setValues(values) method.

Reference link:

Upvotes: 1

Related Questions