Google Apps Script - How do I transfer multiple data rows after search, from data sheet to form, if-CodePudding

I'm building a stock inventory system for carpet retail and have managed pretty well so far but am absolutely stuck with a simple find and transfer code.

The ws has several sheets but the two of interest here are "Sales Form" and "Sales" sheet. both have corresponding data headings such as "Invoice Number", "Product Name", "Quantity", "Date of Sale" etc. They also have check boxes which indicate if the product needs to be cut or if it has been fitted.

The Sales Form is based on inputting data from a flooring sale and transferring it to the Sales sheet - no problem here. Also no issues clearing the form, inserting today's date or finding the first product from a set invoice number in a search.

The idea is to enter an invoice number into the Sales Form sheet; look for the corresponding invoice number on the Sales sheet; then return all product info found back to the Sales Form (to edit, check, review, etc).

But once the script sends the first 'found product' I can't get it to then move to the next line and search, or the next or the next and so on...

Originally, the "return" at the end of the function was not there and I only had the last item found returned to display on the search form.

 function searchSales(){

   var ss=SpreadsheetApp.getActiveSpreadsheet()

   var shUserForm = ss.getSheetByName("Sales Form")
   var shSalesSheet = ss.getSheetByName("Sales")

   var sValue = shUserForm.getRange("E5").getValue();

   var sData = shSalesSheet.getDataRange().getValues();

   for (var i = 0; i < sData.length; i  ){
       var row = sData[i];
   if(row[0] == sValue){
     shUserForm.getRange("C5").setValue(row[3]); //Transfer Sales Date
     shUserForm.getRange("C9").setValue(row[1]); //Transfer Product Name
     shUserForm.getRange("E9").setValue(row[2]); //Transfer Qty
     if(row[4] == true){
       shUserForm.getRange("G9").setValue(false); //Transfer If Cut or Not
     }
     if(row[5] == true){
       shUserForm.getRange("J9").setValue(true); //Transfer If Fitted or Not
     }
     return
   }
 }}

Please help!

CodePudding user response：

Try to increment currentRow as follows (I expect there is a blank line between each item)

function searchSales() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var shUserForm = ss.getSheetByName("Sales Form")
  var shSalesSheet = ss.getSheetByName("Sales")
  var sValue = shUserForm.getRange("E5").getValue();
  var sData = shSalesSheet.getDataRange().getValues();
  var currentRow = 9
  for (var i = 0; i < sData.length; i  ) {
    var row = sData[i];
    if (row[0] == sValue) {
      shUserForm.getRange("C5").setValue(row[3]); //Transfer Sales Date
      shUserForm.getRange("C"   currentRow).setValue(row[1]); //Transfer Product Name
      shUserForm.getRange("E"   currentRow).setValue(row[2]); //Transfer Qty
      if (row[4] == true) {
        shUserForm.getRange("G"   currentRow).setValue(false); //Transfer If Cut or Not
      }
      if (row[5] == true) {
        shUserForm.getRange("J"   currentRow >).setValue(true); //Transfer If Fitted or Not
      }
      currentRow  = 2
    }
  }
}

CodePudding user response：

Here's an optimized solution to retrieve and display the data all at once:

function searchSales() {

  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const shUserForm = ss.getSheetByName("Sales Form")
  const shSalesSheet = ss.getSheetByName("Sales")

  const searchValue = shUserForm.getRange("E5").getValue()
  const salesData = shSalesSheet.getDataRange()
                                .getValues()

  const targetSales = salesData.filter(row => row[0] === searchValue)

  if (targetSales.length) {

    const formData = []
    
    targetSales.forEach(item => {
      const toCut = (item[4] === true) ? false : true
      const rowData = [item[1], ``, item[2], ``, toCut, ``, ``, item[5]]
      formData.push(rowData, Array(rowData.length).fill(``))
    })

    shUserForm.getRange(`C5`).setValue(targetSales[0][3])
    shUserForm.getRange(9, 3, formData.length, formData[0].length)
              .setValues(formData)

  }

}