Google apps scripts set cell formulas needing row number for a row added via appendrow

1
votes

I have a google sheet that is populated with activities via a google form. One of the fields is an activity date field. Once done, the activity is moved to another sheet for reporting purposes via appendrow. 

var cells = [activityDate, title, completionDate, ...];
destsheet.appendRow (cells);

To aid in the population of a pivotTable, one of the columns in the destination spreadsheet is the month of the activity for grouping purposes which is done via the following formula =Text(A1, "MMMM"). The issue I have is that to fill the "row" completely, I need to know the row number of the row added to the destination sheet. For example, say I append row 89, then I can set the formula for the row to =Text(A89, "MMMM"). The issue I'm running into is that while appendRow is atomic, it returns the worksheet, not the row added.

While I could get the row via getRange after appendRow, I imagine its possible that another user could insert a row between the appendRow and the getRange calls. I have solved my problem by changing the formula for the column, to be "=Text(Indirect(Address(Row(), 1, 4)), "MMMM")". This gets me the properly formatted month for the activity date for reporting purposes. For the less spreadsheet savvy, the formula I'm using for my solution is a bit confusing.

Is there a way I can get the appendRow in an atomic transaction so I can properly configure cells in the row that may be "row" number dependent?

1
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Use Lock Service – Cooper

1 Answers

0
votes

How about this? Delete all of the empty rows in your target sheet, then in your append row function call the getMaxRows() method in the same transaction that you append the row. Google appscript is synchronous so another row cannot be inserted in the moment it takes to get the max row information. 

function appendRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var row = sheet.appendRow(['Some data'])
  Logger.log(row.getMaxRows())
}