CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
danniel
danniel

Reputation: 31

How to update row on another sheet based on ID without overwriting with blank cells?

I'm trying to copy rows from one sheet to another using Google Apps Script, based on a matching ID in Column 4 of each, but I can't seem to find how to paste it into the matching row and do this without overwriting existing data with blank cells.

In Google Sheets, Sheet1 contains rows of new data defined by an ID. Sheet2 should collect every single row, updating rows with the new data if the ID of the new row on Sheet1 matches an existing row on Sheet2. At the end of the day, Sheet1 is cleared, and all that remains is the most up-to-date info on each ID-item in Sheet2. Sometimes rows on Sheet1 aren't filled out completely, in which case, only the cells with data (from Sheet1) should be overwritten on Sheet2.

New IDs will be appended, but I've already more or less worked out how to do that, and it's not part of the question :)

function myFunction() {
    // Path to Correct Spreadsheet
  var ss = SpreadsheetApp.getActive();
  var sheet1 = ss.getSheetByName("Sheet1");
  var sheet2 = ss.getSheetByName("Sheet2");
  var sheet3 = ss.getSheetByName("Sheet3"); //temporary, while I work out how to copy it to the right row in Sheet2

  // Get size of ranges
  var header = 1;  // Number of rows of header info (to skip)
  var range1 = sheet1.getDataRange();
  var data1 = range1.getValues();
  var range2 = sheet2.getDataRange();
  var data2 = data2 = range2.getValues();

      Logger.log('data1 = ' + data1);

  var resultArray = [];
  for(var n=header; n < data1.length ; n++){
    var keep = false;
    for(var p=header; p < data2.length ; p++){
      Logger.log(data1[n][0]+' =? '+data2[p][0]);
      if( data1[n][3] == data2[p][3]){
        resultArray.push(data1[n]);
        Logger.log('true');
        break ;
      }
    }
  }  
  sheet3.getRange(+1,1,resultArray.length,resultArray[0].length).setValues(resultArray);
}

I've managed to cobble together code that finds the matching IDs, and sends the entire row to Sheet3 (for now, before working out how to send it to the right row in Sheet2). But this overwrites any existing data with blank cells (if there are any), and I can't seem to make {contentsOnly: true} to work (is this even the right approach?)

I've only been doing this for a week, trying hard, but out of my depth. Any help, even just pointers in the right direction of topics to learn, would be greatly appreciated. Been living on stackoverflow for the past 48 hours straight trying to find something similar to this, but I couldn't find anything (maybe I'm not even searching the right thing!). Very humbling.

Upvotes: 2

Views: 615

Answers (1)

Cooper
Cooper

Reputation: 64140

try this:

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var sheet1 = ss.getSheetByName("Sheet1");
  var sheet2 = ss.getSheetByName("Sheet2");
  var sheet3 = ss.getSheetByName("Sheet3");
  var header = 1;  // Number of rows of header info (to skip)
  var range1 = sheet1.getDataRange();
  var data1 = range1.getValues();
  var range2 = sheet2.getDataRange();
  var data2 = data2 = range2.getValues();
  var resultArray = [];
  for(var n=header; n < data1.length ; n++){
    var keep = false;
    for(var p=header; p < data2.length ; p++){
      if( data1[n][3] == data2[p][3]){
        var tA=[];
        var rg=sheet2.getRange(p+1,1,1,sheet2.getLastColumn());
        for(var i=0;i<data1[0].length;i++) {
          tA.push((data1[n][i])?data1[n][i]:data2[p][i]);
        }
        rg.setValues([tA]);
        break ;
      }
    }
  }  
}

Upvotes: 1

Related Questions