Append Row to spreadsheet causing extra blank rows to be appended

[B Falk]

I'm trying to add rows to a spreadsheet through an adwords script which runs every day.

Some of the code so far is:

var report_iter = AdWordsApp.report(

    'SELECT ' + columns_str +

    'FROM ACCOUNT_PERFORMANCE_REPORT ' +

    'DURING YESTERDAY', {

      apiVersion: 'v201302'

    }

  ).rows();

     

  while(report_iter.hasNext()) {

    var row = report_iter.next();

    var row_array = [""]; // This is deliberate to include an empty cell in column A in the spreadsheet.

    for(var i in columns) {

      row_array.push(row[columns[i]]); 

      

    }

    sheet.appendRow(row_array);

  }

It is working properly, however, it has some unwanted outcomes as well. What I want to stop is every time I append a row to the bottom of the spreadsheet, it also appends 50 other rows of blank cells. Then, the next time I append a row, it appends it to the bottom of the spreadsheet, 50 rows after the one before... Is there any way to stop this. I had a look at using feed lists but I don't know how to do that or if you can with adwords scripts.

The only other thing I was thinking was to add a new row to the bottom every time, find the row number, then get the row number and insert a row.

[Alexander Wang]

Hi Brendan,

This is an issue with SpreadsheetApp. They have some support here if you want to try and get appendRow to fit your use case. I do think you can rewrite your code to work around it though.

Sheet has a few helper methods that can help you out. Notably, getLastRow() and getMaxRows(). By using these methods you can avoid calling appendRow unless you need to. I believe something like this should work:

if (sheet.getLastRow() == getMaxRows()) {
  sheet.appendRow(row_array)
} else {
  var range = sheet.getRange(sheet.getLastRow() + 1, num_columns);
  range.setValues([row_array]);
}

Another option would be to track the row you are on and just make a call to appendRows whenever the row's index is >= the max rows.

Cheers,

Alex

[Brendan Falk]

Awesome. Thanks Alex.

That's great!

One more thing, how would i change my timezone in adwords?

Cheers,

[Alexander Wang]

Hi Brendan,

I found this help center article that I think will help:

https://support.google.com/adwords/answer/1704358?hl=en

Looks like you can only change it once, so make sure you are sure. In the future, these questions are better guided towards AdWords support/forums. I'm more than happy to help, but they will know more about these sorts of things.

Cheers,

Alex

[Brendan Falk]

Hi Alex,

Thanks very much I'll look into it!

Brendan