0

I have a list of tasks, and I want to create a macro to add a row bellow the last item to that list. But all the macros that i've tried didnt work. Can someone help me with this?

here it is:

function AddRow() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(spreadsheet.getCurrentCell().getRow() + 22, 1, 1, sheet.getMaxColumns()).activate();
  spreadsheet.getActiveSheet().insertRowsAfter(spreadsheet.getActiveRange().getLastRow(), 1);
  spreadsheet.getActiveRange().offset(spreadsheet.getActiveRange().getNumRows(), 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getCurrentCell().offset(0, 8).activate();
  spreadsheet.getCurrentCell().setFormulaR1C1('=TODAY()');
  spreadsheet.getCurrentCell().offset(0, -2).activate();
  spreadsheet.getActiveRangeList().setBorder(null, null, null, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID);
  spreadsheet.getCurrentCell().offset(5, 0).activate();
};
Danillo Villas Neto
  • 51
  • 5
  • Perhaps if you showed us one of the macros that did NOT work, we could help you make it work. – Tedinoz Feb 05 '19 at 22:41
  • sorry @Tedinoz i've edited the question – Danillo Villas Neto Feb 06 '19 at 14:11
  • Thanks for the code. It works but I can see why you're not happy with it. To start, insert some code (after line 3) to find the [last row](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getlastrow), the [last column](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getlastcolumn): `var lastRow = sheet.getLastRow();`, `var lastColumn = sheet.getLastColumn();` – Tedinoz Feb 07 '19 at 03:51
  • `spreadsheet.getCurrentCell()` could be any cell; `sheet.getMaxColumns()` returns blank as well as non-blank columns. You need more finesse. Delete the next three lines `spreadsheet.getActiveSheet().insertRowsAfter(spreadsheet.getActiveRange().getLastRow(), 1);`, `spreadsheet.getActiveRange().offset(spreadsheet.getActiveRange().getNumRows(), 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();`, `spreadsheet.getCurrentCell().offset(0, 8).activate();`. Then insert just one line `var newtaskRange = sheet.getRange(lastRow+1, 1, 1, lastColumn);`: this defines the row after the last row. – Tedinoz Feb 07 '19 at 04:06
  • Lastly: `setFormulaR1C1('=TODAY()')` will **always** display the current date, not the date when you set it. Delete `spreadsheet.getCurrentCell().setFormulaR1C1('=TODAY()');` and read (https://stackoverflow.com/a/25347809/1330560) for an example of how to insert a fixed date into a cell. The rest of your code can be deleted too, and then read up on [How to write in cells in GoogleSpreadSheet using app Script?](https://stackoverflow.com/questions/22897601/) (or Google on the same subject - i.e. do some research). – Tedinoz Feb 07 '19 at 04:14

0 Answers0