Having a hard time with the below Google Scripts JS code. Here's what it's supposed to do. Copy and paste info from active sheet from column M to column AA Go to sheet 2, get the last cell with data and add one row before pasting the new information. If nothing is there then paste to the top of the

Adding Variables to getRange()

Having a hard time with the below Google Scripts JS code.

Here’s what it’s supposed to do.

Copy and paste info from active sheet from column M to column AA
Go to sheet 2, get the last cell with data and add one row before pasting the new information. If nothing is there then paste to the top of the sheet
Upon first paste there is no active cells yet as its a fresh sheet so the info should be pasted directly at the top

This line here is giving me the trouble, I want to put in my last_row variable into getRange. Some docs say you should be able to do something like spreadsheet.getRange("A:"last_row) but that isnt working for me. Here’s the current line, spreadsheet.getRange("A1").activate();

function NEW() {
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRange('M1:AA12').activate();
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
      spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
      var last_row = spreadsheet.getCurrentCell().getRowIndex();
      spreadsheet.getRange("A1").activate();
      spreadsheet.getRange(''MAIN SHEET'!M1:AA12').copyTo(spreadsheet.getActiveRange(), 
      spreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    };

So essentially I am trying to paste data from one sheet to a second sheet. Upon paste I need the macro to find the last row with data in it from the previous paste and add one row so new data gets pasted below without any data overlapping.

Any ideas?

Answer

Regarding your 4th point:

This line here is giving me the trouble, I want to put in my last_row variable into getRange. Some docs say you should be able to do something like spreadsheet.getRange("A:"last_row) but that isn’t working for me. Here’s the current line, spreadsheet.getRange("A1").activate();

It’s not working because the concatenation operator is missing:

spreadsheet.getRange("A:" + last_row)

Advertisement

Answer