Auto Copy Data in New Sheet using Save Button

Question

I have data in MRF Sheet. User Fills details of Material required in "MRF" sheet. when he is done with the details, he clicks on save button. By click on save button, data entered in "MRF" sheet copied to "MRF DATA" sheet. Its not working properly. I want to copy data in A2 row and onwards each time user click on Save button.

Link of sheet is :

https://docs.google.com/spreadsheets/d/18Y6k7iXh1LRjSnn3m0kxvGKR6-BhyOy_axhdk-ResXU/edit?usp=sharing

The script I am using is:

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("MRF");
  var pasteSheet = ss.getSheetByName("MRF DATA");

  // get source range
  var source = copySheet.getRange(10,1,100,6);
  // get destination range
  var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,1,12,2);

  // copy values to destination range
  source.copyTo(destination);

  // clear source values
 //source.clearContent();
}

Answer 1

Issue:

• You have formulas in column A in the MRF DATA sheet and therefore pasteSheet.getLastRow() returns the last row with content even if that contains a formula.

Workarounds:

Workaround 1:

You can count the last row based on the non-blank elements of column A using the filter() function.

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("MRF");
  var pasteSheet = ss.getSheetByName("MRF DATA");
  var pasteSheet_size=pasteSheet.getRange('A:A').getValues().filter(String).length;
  
  // get source range
  var source = copySheet.getRange(10,1,100,6);
  // get destination range
  var destination = pasteSheet.getRange(pasteSheet_size+1,1,12,2);

  // copy values to destination range
  source.copyTo(destination);

  // clear source values
 //source.clearContent();
}

Workaround 2:

You can clear all the formulas and cells in the MRF DATA sheet to make sure that getLastRow() returns the correct number as the last row with content.

Recommended Solution:

Your code gets a hard copy range of the source MRF sheet. This is not ideal if in the future the size of that range changes, and also you are getting unnecessary content (empty dropdown lists).

I also assume that you want to copy only the contents of the source sheet to the destination sheet. Therefore, a better idea would be to use source.copyTo(destination,{contentsOnly:true}); or getRange().setValues() but I will stick to your approach.

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("MRF");
  var pasteSheet = ss.getSheetByName("MRF DATA");
  
  var pasteSheet_size=pasteSheet.getRange('A:A').getValues().filter(String).length;
  var source_size=copySheet.getRange('A10:A').getValues().filter(String).length;
   
  // get source range
  var source = copySheet.getRange(10,1,source_size,6);
  // get destination range
  var destination = pasteSheet.getRange(pasteSheet_size+1,1,source_size,6);

  // copy values to destination range with copyTo
  source.copyTo(destination,{contentsOnly:true}); //copy only the values

  // copy values to destination range with setValues()
  // destination.setValues(source.getValues());

  // clear source values
 //source.clearContent();

}