Home > database >  Macro / Google Sheets - How to edit a pasted row automatically?
Macro / Google Sheets - How to edit a pasted row automatically?

Time:12-11

I have code that duplicates (copies and pastes) a selected row to the bottom of a table. But I am struggling to add more functions to this code.

After pasting the row into the bottom of the list, I need that:

  1. The row background is coloured with # fff2cc

  2. The first cell updates to today's date

  3. The content of the cell on column K is deleted

The code I have currently returns the error, "spreadsheet is not defined", but I'm struggling to correct it.

function Reschedule() {
  var activeRange = SpreadsheetApp.getActiveRange();
  var sheet = activeRange.getSheet();
  var row = sheet.getRange("A1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()   1;
  var dst = sheet.getRange(row, 1);
  activeRange.offset(0, 0, 1, sheet.getLastColumn()).copyTo(dst, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

//the above code works perfectly to copy the selected row and paste it on the bottom of the list, but I need to adjust the code below to make it work with the above. 

  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, 
  sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setBackground('#fff2cc');
  spreadsheet.getCurrentCell().activate();
  var date = new Date();
  date.setHours(0, 0, 0, 0);
  spreadsheet.getActiveRangeList().setValue(date);
  spreadsheet.getCurrentCell().offset(0, 10).activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};

Does anybody know if it is possible? I'm open to other alternatives as well.

CodePudding user response:

You could modify the last part with this code that does your desired steps in your proposed order:

function Reschedule() {
  var activeRange = SpreadsheetApp.getActiveRange();
  var sheet = activeRange.getSheet();
  var row = sheet.getRange("A1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()   1;
  var dst = sheet.getRange(row, 1);
  activeRange.offset(0, 0, 1, sheet.getLastColumn()).copyTo(dst, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

//the above code works perfectly to copy the selected row and paste it on the bottom of the list, but I need to adjust the code below to make it work with the above. 
  sheet.getRange(row,1,1,sheet.getLastColumn()).setBackground("#fff2cc")
  sheet.getRange(row,1).setValue(new Date())
  sheet.getRange(row,11).clearContent()
};
  • Related