Google Sheets Macro not completing

Question

I'm trying to parse a CSV file using Google Sheets macros. I've recorded all the steps as individual macros, and one by one they work fine, but when I combine into one macro, it doesn't run properly. The point at which it stops working is after the PODdateformatting part has completed and it's run through the first three lines of Daystodeliverformula. Cell H2 is populated with the formula, but the formula doesn't then autofill down the rest of the column. Any ideas? Or indeed, am I going about this all wrong and need a good talking to? :-)

function TheWholeShebang() {
  var spreadsheet = SpreadsheetApp.getActive(); // start of DeletedUnwantedColumns
  spreadsheet.getRange('AA:DE').activate();
  spreadsheet.getActiveSheet().deleteColumns(spreadsheet.getActiveRange().getColumn(), spreadsheet.getActiveRange().getNumColumns());
  spreadsheet.getRange('W:X').activate();
  spreadsheet.getActiveSheet().deleteColumns(spreadsheet.getActiveRange().getColumn(), spreadsheet.getActiveRange().getNumColumns());
  spreadsheet.getRange('R:U').activate();
  spreadsheet.getActiveSheet().deleteColumns(spreadsheet.getActiveRange().getColumn(), spreadsheet.getActiveRange().getNumColumns());
  spreadsheet.getRange('H:P').activate();
  spreadsheet.setCurrentCell(spreadsheet.getRange('P1'));
  spreadsheet.getActiveSheet().deleteColumns(spreadsheet.getActiveRange().getColumn(), spreadsheet.getActiveRange().getNumColumns());
  spreadsheet.getRange('A:E').activate();
  spreadsheet.setCurrentCell(spreadsheet.getRange('E1'));
  spreadsheet.getActiveSheet().deleteColumns(spreadsheet.getActiveRange().getColumn(), spreadsheet.getActiveRange().getNumColumns()); // end of DeletedUnwantedColumns

  var spreadsheet = SpreadsheetApp.getActive(); // start of Addcolumnsandheaderlabels
  spreadsheet.getRange('A:F').activate(); 
  spreadsheet.getActiveSheet().insertColumnsAfter(spreadsheet.getActiveRange().getLastColumn(), 6);
  spreadsheet.getActiveRange().offset(0, spreadsheet.getActiveRange().getNumColumns(), spreadsheet.getActiveRange().getNumRows(), 6).activate();
  spreadsheet.getRange('G1').activate();
  spreadsheet.getCurrentCell().setValue('POD Date (formatted)');
  spreadsheet.getRange('H1').activate();
  spreadsheet.getCurrentCell().setValue('Days to Deliver');
  spreadsheet.getRange('G2').activate(); // end of Addcolumnsandheaderlabels

  var spreadsheet = SpreadsheetApp.getActive(); // start of PODdateformatting
  spreadsheet.getRange('G2').activate()
  .setFormula('=DATE(LEFT(D2,4),mid(D2,5,2),right(D2,2))');
  spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES); // end of PODdateformatting

  var spreadsheet = SpreadsheetApp.getActive(); //start of Daystodeliverformula
  spreadsheet.getRange('H2').activate()
  .setFormula('=NETWORKDAYS(E2,G2,Instructions!$B$15:$B$40)-1');
  spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES); // end of Daystodeliverformula
};

Can you post a link to a test sheet with the above code? The code is clumsy being produced by combining macros and could be reduced a lot. AutoFilltoNeighbour depends on data in adjacent column so its hard to see just from code what is happening.... — bcperth
@bcperth Thanks for the reply - I fully expected it to be viewed as messy as I basically recorded a bunch of individual macros (each of which works in isolation) and them lumped them together into one to see if I could do it all in one operation!) Link to test spreadsheet is here: docs.google.com/spreadsheets/d/… — biddlywiddly
I tested extensively but could not make it work. See here for a similar question stackoverflow.com/questions/52229644/…. see additional comment below... — bcperth
I think there is a problem in Google scripts with running multiple autoFillToNeighbor() methods in succession in a script. WHen I ran thewholeshebang() H2 was not autofilled. But when I ran Daystodeliverformula() separately after TheWholeShebang() it filled H correctly. Go figure! This is a google scripts bug unless there is something documented we are both missing.... I also put delays between the autoFillToNeighbor() but made no difference. — bcperth
Ok I found a way to make it all work.. see reply below. Comments above are still applicable but see more notes in the reply... — bcperth

bcperth bcperth · Accepted Answer · 2018-11-02T02:07:07

After messing about with range.autoFillToNeighbor() and failing (see comments above) I was still suspicious about that method.

When I used this the range.autoFill() method instead it all worked fine. See code below.

  // start of PODdateformatting
  //var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('G2').activate().setFormula('=DATE(LEFT(D2,4),mid(D2,5,2),right(D2,2))');
  var sourceRange = spreadsheet.getRange("G2:G2");
  var destination = spreadsheet.getRange("G2:G369");
  sourceRange.autoFill(destination,SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

  //start of Daystodeliverformula
  var spreadsheet = SpreadsheetApp.getActive();
  var range = spreadsheet.getRange('H2').activate().setFormula('=NETWORKDAYS(E2,G2,Instructions!$B$15:$B$40)-1');
  var sourceRange = spreadsheet.getRange("H2:H2");
  var destination = spreadsheet.getRange("H2:H369");
  sourceRange.autoFill(destination,SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

In hindsight I now believe that range.autoFillToNeighbor() was never right for your use case in the first place!

range.autoFillToNeighbor() expects to build autofill formulas based on data that is contained in neighbouring columns. It tries to do this intelligently, but neighbouring columns do not contain anything useful! Its amazing it ever worked... maybe sometimes defaulting to range.autoFill()!

range.autoFill() on the other hand, just duplicates the formula (data) above or below ( NOT looking to neighbouring cells for help).

You can copy and paste these over the corresponding functions in function TheWholeShebang() and should all work.

Note I assume a fixed range of 369 as per your data, but you can calculate this based on actual size if you prefer, in case no of rows is changed.

Google Sheets Macro not completing

1 Answers