CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
hungggggggggggg
hungggggggggggg

Reputation: 23

Problem with recording data change in google sheet

I have two sheets. On one sheet (Sheet1), I have a column 'A' which has 5 fixed values, and a column 'B' which records the timestamp value in 'A' change. The other sheet (Sheet2) is meant to record all the changes in the first sheet in one day.

I use this simple query in the recording sheet:

=QUERY(Sheet1!A$1:X,"select * where C>="& Sheet2!D1)

with Sheet1!C has timestamps and Sheet2!D1 is the timestamp of 12:00 AM today

The problem is when I change the value of a row in C columns more than one time, instead of creating a new row in Sheet2 it change the value of that row in Sheet2 into new values.

So how do I change my code to get my desire results? sheet1 data

desired output

EDIT 2: here is my new code, but it doesn't help.

function importdata(x) {
  // var row = x.range.getRow();
  // var col = x.range.getColumn();

  var addedsheet=SpreadsheetApp.getActive().getSheetByName("Change"); // Sheet where I want to keep the record of the change
  var original_sheet = SpreadsheetApp.getActive().getSheetByName("Master"); //sheet where the change is happended
  var compared_value = addedsheet.getRange(1,4).getValue(); // Cell D1 of sheet "Change", which has timestamp of today

  var insert_area = original_sheet.getRange("A2:X").getValues() // area to get value from "Master" sheet to put into "Change"

  var compared_area = original_sheet.getRange("C2:C").getValues(); // area where has timestamp

  if (compared_area >= compared_value){
    
    addedsheet.values.append([insert_area])}

} //if timestamp of one row from Master is greater than the value at Change!D1 =>append that row at the end (this is what I'm trying to do)

EDIT 3: I fixed the above code by append[insert_area][0] instead of [insert_area] But then I have a new problem: there will a chance that a row in sheet 1 will be overwrited in sheet 2. I try something like this, but it returns nothing on the sheet.

function for_each_row(){
 var addedsheet=SpreadsheetApp.getActive().getSheetByName("Change"); // Sheet where I want to keep the record of the change
  var original_sheet = SpreadsheetApp.getActive().getSheetByName("Master"); //sheet where the change is happended
  var compared_value = addedsheet.getRange(1,4).getValue(); // Cell D1 of sheet "Change", which has timestamp of today
  var number_of_row_2 = addedsheet.getLastRow;
  var number_of_row_1 = original_sheet.getLastRow();


  for (var i=2; i<number_of_row_1 +1; i++){
    var compared_stamp = original_sheet.getRange("C"+i).getValues(); 
    var insert_values = (original_sheet.getRange(i,1,1,24).getValues())
    if (compared_stamp > compared_value){ 
      
      var insert_values = (original_sheet.getRange(i,1,1,24).getValues()); 
      for (var j = 2; j<number_of_row_2 +1; j++){ 
      
       var value_from_sheet = addedsheet.getRange(j,1,1,24).getValues(); 
      
       
       if (insert_values ===value_from_sheet){ 
         
         return 
       }
       else(
       addedsheet.appendRow(insert_values[0])) 
       

    }
  }
  }
 }

My thought is if a row satisfies the 1st condition then the value will be check in sheet 2. If sheet 2 didn't have that row then append that row.

Upvotes: 1

Views: 142

Answers (1)

Iamblichus
Iamblichus

Reputation: 19309

Issue:

If I understand you correctly, you want to do the following:

  • If sheet Master is edited, iterate through all rows in this sheet (excluding headers), and for each row, check the following:
    1. Column C has a higher value than cell D1 in sheet Change.
    2. This row, with these exact values, does not exist in Change.
  • If these conditions are meet, append the row to Change.

Solution:

Use filter and some to filter out rows that don't match your two conditions, and use setValues to write the resulting rows to your other sheet.

Code snippet:

function onEdit(e) {
  var editedSheet = e ? e.range.getSheet() : SpreadsheetApp.getActiveSheet();
  if (editedSheet.getName() === "Master") {
    var addedSheet = SpreadsheetApp.getActive().getSheetByName("Change");
    var compared_value = addedSheet.getRange(1,4).getValue();
    var newData = editedSheet.getRange("A2:X" + editedSheet.getLastRow()).getValues();
    var currentData = addedSheet.getRange("A2:X" + addedSheet.getLastRow()).getValues();
    var filteredData = newData.filter(row => row[2] >= compared_value)
                              .filter(row => !currentData.some(currentRow => JSON.stringify(currentRow) === JSON.stringify(row)));
    addedSheet.getRange(addedSheet.getLastRow()+1,1,filteredData.length,filteredData[0].length).setValues(filteredData);
  }
}

Note:

  • An easy way to check if two rows have the same values is using JSON.stringify(row).
  • I'm assuming the timestamps are not Dates. If they are, you should compare them using getTime(). So you should change the corresponding code to newData.filter(row => row[2].getTime() >= compared_value.getTime()).

Upvotes: 1

Related Questions