CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
RogerP
RogerP

Reputation: 1

Copying text/numbers from one sheet to below data already in another sheet in google sheets

I have a sheet 'Application' with data on one row, I have created the following script to copy & paste some of the data I want from this sheet into particular cells in the next sheet 'Members' It works, but what I want to do now is add a function so that the next time I run the script it inserts the data into the row below the previously inserted data. I have been trying to use .getLastRow() but with no success, can anyone help please?

function MoveDataMacro() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('Application!D5').copyTo(spreadsheet.getRange('Members!R6').activate(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('Application!E5').copyTo(spreadsheet.getRange('Members!F6').activate(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('Application!F5').copyTo(spreadsheet.getRange('Members!D6').activate(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('Application!G5').copyTo(spreadsheet.getRange('Members!B6').activate(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Members'), true);
  spreadsheet.getRange('B6').splitTextToColumns(SpreadsheetApp.TextToColumnsDelimiter.SPACE);
  spreadsheet.getRange('E6').setFormula('=IF(D6="","",ROUNDDOWN((TODAY()-D6)/365.25,0))');
};

Upvotes: 0

Views: 39

Answers (1)

Cooper
Cooper

Reputation: 64092

Perhaps this is what you require?

function MoveDataMacro() {
  var app=SpreadsheetApp;
  var ss=app.getActive();
  var sh1=ss.getSheetByName('Application');
  var sh2=ss.getSheetByName('Members');
  var nr=sh2.getLastRow()+1;
  sh1.getRange('D5').copyTo(sh2.getRange(nr,18), app.CopyPasteType.PASTE_NORMAL, false);
  sh1.getRange('E5').copyTo(sh2.getRange(nr,6), app.CopyPasteType.PASTE_NORMAL, false);
  sh1.getRange('F5').copyTo(sh2.getRange(nr,4), app.CopyPasteType.PASTE_NORMAL, false);
  sh1.getRange('G5').copyTo(sh2.getRange(nr,2), app.CopyPasteType.PASTE_NORMAL, false);
  sh2.getRange(nr,2).splitTextToColumns(app.TextToColumnsDelimiter.SPACE);
  sh2.getRange(nr,5).setFormula('=IF(D6="","",ROUNDDOWN((TODAY()-D6)/365.25,0))');
}

Upvotes: 1

Related Questions