Warehouse Form

Sales WS #1

Sales WS #2

Project Background

Working towards a dynamic W/B for my work's Stock Inventory.

There are multiple W/S with data and two user forms to search, edit and insert new data to relevant places.

The Warehouse Form is shown above along with the Sales WS needed in this task. I have called up successfully all the records with the field "Cuts" marked as False from the Sales WS to the Userform.

Objective On a button click (allocated to the "All Cut" button shown on the Userform), the aim is to:

a) Find the corresponding row in the Sales WS that matches the given row on the Warehouse Userform. (The code does this successfully using two loops.)

b) In the row found on the Sales WS, change the "Cuts" Column from false to true.

Current Code

//Update to "All Cut" information on the Sales Form
function updateCuts() {
  let ss = SpreadsheetApp.getActiveSpreadsheet()
  let shUserForm = ss.getSheetByName("Warehouse Form")
  let shSalesSheet = ss.getSheetByName("Sales")
  const sData = shSalesSheet.getDataRange().getValues();
  const wData = shUserForm.getRange(9,3,shUserForm.getLastRow() 1,shUserForm.getLastColumn() 1).getValues();

for (var i=9; i<wData.length; i  ) {
  var wsRow = wData[i];
   const sValue = shUserForm.getRange("C"   i).getValue();
   const tValue = shUserForm.getRange("E"   i).getValue();
   const wValue = shUserForm.getRange("G"   i).getValue();
   if(shUserForm.getRange("C"   i).isBlank()==false){
  
     for(var j=0; j<sData.length; j  ) {
       var srow = sData[j];
         if (srow[0]===sValue && srow[1]===tValue && srow[2]===wValue) {
        shSalesSheet.getRange("E"   j).setValue(true) // Change Cut Checkbox from False to True
     }}

   i  = 2}}
   } 

Current Outcome / Issue

The code shown, is changing the "Cuts" column value in the row above the record found, not the record required (shown in image Sales WS #2)

QUESTION

How do I change the code to resolve this?

CodePudding user response：

You need to setValue() at E(j 1) as j is a 0-based index, and the rows are 1-based.

function updateCuts() {

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  const userForm = spreadsheet.getSheetByName("Warehouse Form")
  const salesSheet = spreadsheet.getSheetByName("Sales")

  const salesData = salesSheet.getDataRange().getValues()

  userForm.getRange(9, 3, shUserForm.getLastRow()-8, 10)
          .getValues()
          .filter(row => row[0] !== ``)
          .forEach(entry => {
            const index = salesData.findIndex(row => row[0] === entry[0] && row[1] === entry[2] && row[2] === entry[4])
            if (index !== -1) salesSheet.getRange(`E${index 1}`).setValue(true)
          })

}