The condition to break when a value is found within a specific range on sheet1 if it is present in the range on

Question

The condition to break when a value is found within a specific range on sheet1 if it is present in the range on

Essentially, the user will input data for a new item on the NovoItem sheet. Upon pressing the Save Button (yet to be added), the code should check the ArquivoItens sheet to see if the item already exists. If it does, the code should halt its operation. However, currently, the code is not halting when a match is found on the ArquivoItens sheet:

function copyrange() { 
  var sourceSheet = 'Novo Item';
  var destinationSheet = 'ArquivoItens';
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName(sourceSheet);
  var ActiveUser = Session.getActiveUser();

  //This part adds the current user and a timestamp to the last two columns of sheet1.
  var val = sheet.getRange("Q12:Q")
      .getValues();
  for (var i = 0; i < val.length; i++) {
      if (val[i] > 0) {
        sheet.getRange(12, 34, i + 1, 1)
               .setValue(new Date());
       sheet.getRange(12, 35, i + 1, 1)
               .setValue(ActiveUser)
      }
    }

  var LastRowSource = sheet.getLastRow();
  var LastColumnSource = sheet.getLastColumn();
  var values = sheet.getRange(11,1,LastRowSource,LastColumnSource).getValues();
  var csh = ss.getSheetByName(destinationSheet);
  var data = []; 

  for (var i = 1; i < values.length; i++) {    
    if ( values[i][0] != '') { 
      data.push(values[i]);      
      //sheet.deleteRow(i+1) 
    } 
  }

  var dataNovoItem = sheet.getRange("B12:B").getValues(); 
  var dataArquivoItens = csh.getRange("B2:B").getValues(); 

  for(var n=1; n < dataNovoItem.length ; n++){
    for(var j=1; j< dataArquivoItens.length ; j++){
      if (dataNovoItem[n] != 0 && dataArquivoItens[j] != 0) {
        if(dataNovoItem[n] == dataArquivoItens[j]) {
        break;
        }
      }
  }
    }

  Logger.log("Novo Item" + dataNovoItem);
  Logger.log("ArquivoItens" + dataArquivoItens);

  //Copy data array to destination sheet  
 csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(data);

}

Here's the log output I'm seeing: https://i.sstatic.net/Zdp8T.jpg

Any assistance would be greatly appreciated.

javascript google-sheets spreadsheet

Answer 1

Answer №1

Make sure to utilize the iterator correctly in order to access the index being iterated through.

Try this instead:

for(var i=0; i < newData.length; i++){
    for(var j=0; j < oldData.length; j++){
        if (newData[i] !== 0 && oldData[j] !== 0) { 
            if(newData[i] == oldData[j]) {
                break;
            }
        }
    }
}
Logger.log("New Data: " + newData);
Logger.log("Old Data: " + oldData);

Answer 2

Make sure to utilize the iterator correctly in order to access the index being iterated through.

Try this instead:

for(var i=0; i < newData.length; i++){
    for(var j=0; j < oldData.length; j++){
        if (newData[i] !== 0 && oldData[j] !== 0) { 
            if(newData[i] == oldData[j]) {
                break;
            }
        }
    }
}
Logger.log("New Data: " + newData);
Logger.log("Old Data: " + oldData);

Answer 3

Answer №2

My solution involved iterating through the current list and comparing each row with the specified value on the active sheet. While this may seem straightforward to experienced programmers, it presented a challenge for someone like me who is still a beginner.

Here’s how the process looks now:

var newItemID = sheet.getRange("W5").getValue(); // Obtain the cell where the new item will be placed
var itemIDArquivoItens = csh.getRange(2, 1, csh.getLastRow(),1).getValues(); // Retrieve the range of existing IDs to compare against

var message = "Item already registered!"; // Displayed message if the ID already exists in the list
for(j = 1; j < itemIDArquivoItens.length; j++) { // Loop through existing IDs
  if(itemIDArquivoItens[j][0] == newItemID) { // If the ID exists, show a message and halt the code execution
    Browser.msgBox(message)
    return;
  }
}

Answer 4

My solution involved iterating through the current list and comparing each row with the specified value on the active sheet. While this may seem straightforward to experienced programmers, it presented a challenge for someone like me who is still a beginner.

Here’s how the process looks now:

var newItemID = sheet.getRange("W5").getValue(); // Obtain the cell where the new item will be placed
var itemIDArquivoItens = csh.getRange(2, 1, csh.getLastRow(),1).getValues(); // Retrieve the range of existing IDs to compare against

var message = "Item already registered!"; // Displayed message if the ID already exists in the list
for(j = 1; j < itemIDArquivoItens.length; j++) { // Loop through existing IDs
  if(itemIDArquivoItens[j][0] == newItemID) { // If the ID exists, show a message and halt the code execution
    Browser.msgBox(message)
    return;
  }
}

The condition to break when a value is found within a specific range on sheet1 if it is present in the range on

Answer №1

Answer №2

Similar questions

Preventing runtime error in Next.js API routes by handling axios exceptions

Maximizing the efficiency of React.js: Strategies to avoid unnecessary renders when adding a new form field on a webpage

Tips for implementing Bootstrap pagination in VueJS 2

Utilize emit to distribute / allocate a variable

showing a fading-in effect after a successful AJAX post

Storing the result of an Angular $http.get request in a variable

Tips for executing parallax designs, similar to the techniques used on the

Error message in the browser console: Uncaught TypeError - The function allSections.addEventListener is not recognized

Saving integer data retrieved from DynamoDB in a React Native application

How can you update the options within a select tag depending on the selected index of another select tag using AngularJS?

Here is a unique version: "In Javascript, users can trigger the function this.Something() from within this.img.onload by

IntelliJ coverage for backend JavaScript

Breaking down a string and then retrieving elements from an array

The split() function returns a string that remains unaltered and intact, without any

Object contains a key within another object

What is the best way to distribute a function within a div container?

Javascript does not have the capability to interact directly with HTML code

Distinguishing between el and $el in Backbone.Js: What sets them apart?

Using Passport authentication callback rather than redirecting the user

What is the best way to expand this HTML5 canvas script to fill the entire screen?