CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
KingOtta
KingOtta

Reputation: 35

Google Script - Conditionally append rows from sheet to sheet

I'm trying to append rows from one sheet to another sheet based on two conditions. I'm fairly new to google script and coding in general. You can find a copy of the worksheet here.

I want to extract all rows with a weeknum (column I of MASTER sheet) that matches the previous weeknum (found on the B2 cell of the DASHBOARD sheet) and that also have "Not Coming" (column K of MASTER SHEET). The goal is to create a call list (on the Call List Cancelled W-1) every week with people that did not honor their appointments the week before. Ideally the rows would be appended on the top of the call list sheet.

I've managed to build this code as for now. But it does not work.

// Creating Call List from cancelled appointments in W-1

var ss = SpreadsheetApp.getActiveSpreadsheet();

//CALL LIST
var sheetCallList = ss.getSheetByName("Call List Cancelled W-1");
var rangeCallList = sheetCallList.getDataRange();

//MASTER
var sheetMaster = ss.getSheets()[0];
var dataRangeMaster = sheetMaster.getDataRange();
var valuesMaster = dataRangeMaster.getValues();
var lastRowMaster = sheetMaster.getLastRow();
var cameOrNot = sheetMaster.getRange("K:K").getValues();
var weekNumColumn = sheetMaster.getRange("I:I").getValues()

//DASHBOARD
var sheetDashboard = ss.getSheets()[1];
var weeknum = sheetDashboard.getRange(2, 2).getValue();
var lastweeknum = weeknum - 1

function appendRowsToCallList() {
   for (var i = 2; i < lastRowMaster; i++); {
    if ( lastweeknum == weekNumColumn && cameOrNot == "Not Coming") {
      sheetCallList.rangeCallList.appendRow(valuesMaster);
    }
  }
}

Upvotes: 1

Views: 871

Answers (1)

CMB
CMB

Reputation: 5173

Solution:

There is a typo in your for loop:

for (var i = 2; i < lastRowMaster; i++);

Since there's an extra semicolon, this would loop but execute nothing.

Also, to append rows, you need to refer to individual arrays in valuesMaster and check individual values in the other arrays. Also indexes start with 0 so to check from the second value you start the index with 1.

Function code:

function appendRowsToCallList() {

   for (var i = 1; i < lastRowMaster; i++) {
    if ( lastweeknum == weekNumColumn[i][0] && cameOrNot[i][0] == "Not Coming") {
      sheetCallList.appendRow(valuesMaster[i]);
    }
  }
}

Output:

enter image description here

Upvotes: 1

Related Questions