CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsforeachnested-loops
Caramel
Caramel

Reputation: 19

loop through 2 google sheets

Goodday,

I have 2 sheets. For each ID on sheet1 I need to verify if that ID is also on sheet2 AND if Date Processed is blank.

If both condition are true > today's date should be set in Date Processed.

enter image description here

I've managed to do so for just 1 value (A2) from sheet1. What I need is a way to go through all the values in sheet1. Ideally the row in sheet1 would also get deleted (not sure if that is possible)

This is my code till now

function myMatch(){
var file  = SpreadsheetApp.getActiveSpreadsheet();
var ss    = file.getSheetByName("Sheet1");
var ws    = file.getSheetByName("Sheet2");

var wsData = ws.getDataRange().getValues();
var mySearch = ss.getRange("A2").getValue();

  for(var i = 0; i < wsData.length; i++){
    
    if(wsData[i][1] == mySearch && wsData[i][2] == "")
    {      
      ws.getRange(i+1,3).setNumberFormat('dd"-"mmm"-"yyyy').setValue(new Date());
    }
  }
}

Your help is really appreciated as I have been trying and searching for a solution for 2 days now. Thank you

Upvotes: 0

Views: 156

Answers (2)

Yuri Khristich
Yuri Khristich

Reputation: 14537

I know it doesn't makes much sense. Muhammet's code works and looks just fine. But, rather for fun and educational purposes, here is another "functional" solution:

function myFunction() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const s1 = ss.getSheetByName('Sheet1');
  const s2 = ss.getSheetByName('Sheet2');

  // get data from Sheet1 and Sheet2
  const s1_data = s1.getDataRange().getValues().slice(1,);
  const s2_data = s2.getDataRange().getValues().slice(1,);

  // get IDs from data of Sheet1
  const IDs = s1_data.map(x => x[0]); 
  const IDs_to_delete = []; // here will be IDs to delete

  // function checks and fill a row and adds ID to array to delete
  const write_date = (id,row) => {
    if (row[1] == id && row[2] == '') { 
      IDs_to_delete.push(id); 
      row[2] = new Date();
    }
  }

  // change rows within data of Sheet 2
  IDs.forEach(id => s2_data.forEach(row => row = write_date(id,row)));

  // clear and fill Sheet 2
  s2.getDataRange().offset(1,0).clearContent();
  s2.getRange(2,1,s2_data.length,s2_data[0].length).setValues(s2_data);

  // remove rows from data of Sheet 1
  const s1_data_new = s1_data.filter(row => !IDs_to_delete.includes(row[0]));

  // clear and fill Sheet 1 with new data
  s1.getDataRange().offset(1,0).clearContent();
  s1.getRange(2,1,s1_data_new.length,s1_data_new[0].length).setValues(s1_data_new);

}

The only improvements over Muhamed's code is that this implementation removes processed rows from Sheet1. And, I believe, it will work faster for huge lists, because it doesn't use getRange() and setValue() on every found cell but fills all cells of the sheet at once with setValues() method.

Upvotes: 3

Muhammet Yunus
Muhammet Yunus

Reputation: 589

You need a loop for this. Use

var mySearchs = ss.getRange('A2:A').getValues();

and loop through all values of this array.

function myMatch(){
  var file  = SpreadsheetApp.getActiveSpreadsheet();
  var ss    = file.getSheetByName("Sheet1");
  var ws    = file.getSheetByName("Sheet2");

  var wsData = ws.getDataRange().getValues();
  var mySearchs = ss.getRange('A2:A').getValues();

  mySearchs.forEach((v) => {
    for(var i = 0; i < wsData.length; i++){
      
      if(wsData[i][1] == v && wsData[i][2] == "")
      {      
        ws.getRange(i+1,3).setNumberFormat('dd"-"mmm"-"yyyy').setValue(new Date()); 
      }
    }
  })  
}

Upvotes: 2

Related Questions