I've been trying to loop over a range of data and bring the data to another sheet until the loop hits one of the following words (Tools, Painting) and stop there.

I was going for a for loop while, but I'm not sure this would be the best way to do that.

Here's the code draft I'm working on:

function importPipeworkData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const input4File = ss.getSheetByName('Input Files Mapping').getRange('B4').getValue()
  const pipeworkSheet = SpreadsheetApp.openByUrl(input4File).getSheetByName('Sheet1');
  const inputPipeworkRng = pipeworkSheet.getRange(2, 1, pipeworkSheet.getLastRow(), 4);
  const inputPipeworkData = inputPipeworkRng.getValues();

  const boqPipeworkSheet = ss.getSheetByName('BOQ Pipework');
  const boqPipeworkRng = boqPipeworkSheet.getRange(4, 1, boqPipeworkSheet.getLastRow(), 4);
  //boqPipeworkRng.clear(); //Clears the sheet to receive updated data;

  const pipeworkData = [];

  for (let a = 0; a < inputPipeworkData.length; a  ) {
    while (inputPipeworkData[a][0] != 'Valves') //This is one of the unclear parts

      Logger.log(inputPipeworkData[a][1])
  }
}

Here's an example of the dataset: https://docs.google.com/spreadsheets/d/1W9zPt1nYKv59j9kjgFqy5Z4KPgvXZUoCusTk06kRQUk/edit?usp=sharing

I'd appreciate if any help could be given here.

Cheers, Antonio

CodePudding user response：

In your situation you should use only one loop

Thereby,

• in case a for loop an additional if statement is necessary .
• in case of a while loop, you should make sure to increment your counter variable.

Sample with for loop:

  const keyWords = ["Tools", "Painting"];
  for (let a = 0; a < inputPipeworkData.length; a  ) {
    if (keyWords.indexOf(inputPipeworkData[a][0]) > - 1){
      break;
     }
      Logger.log(inputPipeworkData[a][1]);
      pipeworkData.push(inputPipeworkData[a]);
  }
}

Sample with while loop:

  const keyWords = ["Tools", "Painting"];
  var a = 0;
  while (keyWords.indexOf(inputPipeworkData[a][0]) == - 1){
    Logger.log(inputPipeworkData[a][1]);
    pipeworkData.push(inputPipeworkData[a]);
    a  
  }
}