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