How to copy a range from a mastersheet to the last row of another sheet (Another sheet name = cell value in Mastersheet Z1)

Question

I have a mastersheet named "ENTRATE MAIN", I want to copy range "A2:J50" to the last column of another sheet whose name is found in Cell Z1 in the Mastersheet. But the code needs to check if the data is already copied first.

I'm actually new to Google App Script, so I've actually tried using some formulas but they dont do the job since the data in the master sheet is dynamic.

I have looked at the code in this URL but it doesn't exactly what I want. Copy data from one sheet to the last row of another sheet

I expect to check if the master sheet data is already there in the other sheet. I also need it to copy to the last row of the sheet name in cell Z1 of mastersheet

The code in that URL won't work because it's for Excel, not Google Sheets. Do you have any Google Apps Script you've tried/adapted for this purpose? — ross, Jul 22 '19 at 07:58
None. As I said, I'm not so vast in Google App script. I was thinking of recording a macro but I later found out that I'll need lastRow and dynamic sheets issues — Smith O., Jul 22 '19 at 08:44

Jescanellas · Answer 1 · 2019-07-22T15:09:53.380

Maybe this is what you are looking for. Keep in mind that it's not possible to check for the Master data in the second sheet without using the name in Z1, unless you manually input the name of the second Sheet in the code first.

function main(){
  //Master Sheet
  var sprsheet = SpreadsheetApp.getActiveSpreadsheet();
  var master_sheet = sprsheet.getSheetByName("ENTRATE MAIN");
  var master_range = master_sheet.getRange("AJ2:J50");
  //Second Sheet
  var sheetName = master_sheet.getRange("Z1").getValue();
  var second_sheet = sprsheet.getSheetByName(sheetName);
  var lastrow = second_sheet.getLastRow();

  var master_data = master_range.getValues();

  if (lastrow > 50){ //This is in case your second sheet has less than 50 rows before copying the data
    //We have to check the previous 49 rows in case the data is already there
    var second_range = second_sheet.getRange("AJ"+(lastrow-49)+":J"+(lastrow)); 
    var second_data = second_range.getValues();

    if (!isCopied(master_data, second_data)){
      //Data is not there so we copy it in the next 49 rows
      //If you want to overwrite the last row, just remove the +1
      second_range = second_sheet.getRange("AJ"+(lastrow+1)+":J"+(lastrow+49));
      second_range.setValues(master_data);

    }
  } else {
    //The second sheet has less than 50 rows so the data is obviously not there
    var second_range = second_sheet.getRange("AJ"+(lastrow+1)+":J"+(lastrow+49));
    second_range.setValues(master_data);

  }
}

function isCopied(master_data, second_data){
  for (i in master_data){
    for (j in master_data[i]){
      if (master_data[i][j] != second_data[i][j]){
        return false;
      }
    }

  }
  return true;
}

How to copy a range from a mastersheet to the last row of another sheet (Another sheet name = cell value in Mastersheet Z1)

1 Answers1