.getDataRange not the true range after a .setFormula

Question

I have a function that:

finds last row in a sheet,
inserts a query into that sheet at last row plus one which works fine and new records are appended (uses setFormula),
executes a Spreadsheet App flush to force all pending changes to update so can get solid ranges afterward so i thought!

HERE IS THE PROBLEM: I want to get values from the new resulting range and paste the values back in; however the .getDataRange returns the original .dataRange and not the new one.

`//get new ranges after formula run
var SheetNow = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("New Records");
var rangeNow = SheetNow.getDataRange();
var recordsNow = RangeNow.getValues();
rangeNow.setValues(recordsNow);`

THIS does not work as it only gets me the original data range of when the function was run and will not reflect the resulting larger range with the new records added by the inserted formula.

I'm confused why the getDataRange is not accurate even after a flush and even after declaring new variables to get the sheet and range.

Any ideas please?

KRR KRR · Accepted Answer · 2014-11-14T23:02:30

Tried to implement it this way and it worked well for me.

function getNewFormulaRange(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var last_row = sheet.getLastRow();
  var last_column = sheet.getLastColumn();
  Logger.log('1: ' + last_row);
  var range_input = sheet.getRange(1,1,last_row,last_column);
  var cell = sheet.getRange(last_row+1,10);
  cell.setFormula("=SUM(B3:B4)");
  SpreadsheetApp.flush();
  var last_row = sheet.getLastRow();
  var last_column = sheet.getLastColumn();
  var new_range = sheet.getDataRange();
  Logger.log(new_range.getLastColumn());
  Logger.log(last_row);
  Logger.log(' col: ' + last_column);
}

Also I see there is a typo in your code provided at 'var recordsNow = RangeNow.getValues();' instead it should be 'var recordsNow = rangeNow.getValues();'.

Hope that helps!!

.getDataRange not the true range after a .setFormula

1 Answers