appendRow() from MCC report with columns array

[Stella M. Meyer]

Hi Forum Team, 

I want to report from MCC level to a spreadsheet with the appendRow() method

Normally I would do it like this (imagine columns, reportType, whereStringText, campaignType and dateRange to be variables): 

var rawData = AdsApp.report(
        "SELECT " + columns +
        " FROM " + reportType +
        whereStringText +
        campaignType +
        " DURING " + dateRange);
      
    var rows = rawData.rows();
    while (rows.hasNext()) {
    var row = rows.next();
    
    data.appendRow([row["Week"],row["AccountDescriptiveName"],row["CampaignName"]]);
    
  }

 Now I want the columns (for instance "Week", AccountDescriptiveName", etc.) be pulles from an array. The length of the array is not predefined and may change over time. 

I think I kind of need an array in an array with a for loop (or two), but everything I tried didn't work. 

I would appreciate every help here. 

Thank you very much in advance, 

Stella

[Google Ads Scripts Forum Advisor]

Hi Stella,

Thank you for posting your concern.

If you want to store the fields in an array variable and use it to the report, then the initialization or the line of code would be looks like below:
var columns = ['Week','AccountDescriptiveName',CampaignName'];

However, if you encounter any issues with this implementation, could you provide the error or more details to the issue that you encounter with this to further check?

Regards,
Ejay
Google Ads Scripts Team

ref:_00D1U1174p._5004Q1zKdI4:ref

[Stella M. Meyer]

Hi Ejay, 

sorry, I think my request wasn't as clear as I thought. 

Sure, that's how the array would look like, but I am lost how to iterate this array within the while loop for the rows (especially when the length of the array is not predefined), so that I get

data.appendRow([row[array[0]],row[array[1]],row[array[2]]]) etc, 

Thank you for your help. 

Regards, 

Stella

[Google Ads Scripts Forum Advisor]

Hi Stella,

If possible, I'd recommend using the exportToSheet report method instead of the spreadsheet method, appendRow. 

However, if you need to use the appendRow method, you can try the following snippet:
 

//report is a report object (AdsApp.report)
  
  var rows = report.rows();

  while(rows.hasNext()) {
    var row = rows.next();
    var rowContents = [];
    var arrayOfColumns = columns.split(",");
    arrayOfColumns.forEach(function(val) {
      rowContents.push(row[val])
    });
    Logger.log(rowContents); //Check the rows are formatted as intended
  }

Hope this helps.

Regards,
Matt

[Stella M. Meyer]

Hi Matt, 

thank you for your reply. 

I would love to use exportToSheet(), but as mentioned in my original post, I work on MCC level and I haven't found a way to use exportToSheet() without every new account overwriting the existing data. If you have any ideas, I would love to give it a try. 

However, the output with the snippet you suggested is:

10.5.2020 14:54:46	[Week, AccountDescriptiveName, CampaignName, Cost, Impressions, Interactions, Clicks, AverageCpc, 2020-04-27, null, null, null, null, null, null, null]
10.5.2020 14:54:48	[Week, AccountDescriptiveName, CampaignName, Cost, Impressions, Interactions, Clicks, AverageCpc, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null]
10.5.2020 14:54:52	[Week, AccountDescriptiveName, CampaignName, Cost, Impressions, Interactions, Clicks, AverageCpc, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null]
10.5.2020 14:54:52	[Week, AccountDescriptiveName, CampaignName, Cost, Impressions, Interactions, Clicks, AverageCpc, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null]
10.5.2020 14:55:09	[Week, AccountDescriptiveName, CampaignName, Cost, Impressions, Interactions, Clicks, AverageCpc, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null]
10.5.2020 14:55:43	[Week, AccountDescriptiveName, CampaignName, Cost, Impressions, Interactions, Clicks, AverageCpc, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null]
10.5.2020 14:55:43	[Week, AccountDescriptiveName, CampaignName, Cost, Impressions, Interactions, Clicks, AverageCpc, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null]
10.5.2020 14:55:46	[Week, AccountDescriptiveName, CampaignName, Cost, Impressions, Interactions, Clicks, AverageCpc, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null]
10.5.2020 14:55:47	[Week, AccountDescriptiveName, CampaignName, Cost, Impressions, Interactions, Clicks, AverageCpc, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null]
10.5.2020 14:55:48	[Week, AccountDescriptiveName, CampaignName, Cost, Impressions, Interactions, Clicks, AverageCpc, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null]
10.5.2020 14:55:49	[Week, AccountDescriptiveName, CampaignName, Cost, Impressions, Interactions, Clicks, AverageCpc, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null]
10.5.2020 14:55:50	[Week, AccountDescriptiveName, CampaignName, Cost, Impressions, Interactions, Clicks, AverageCpc, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null]
10.5.2020 14:55:54	[Week, AccountDescriptiveName, CampaignName, Cost, Impressions, Interactions, Clicks, AverageCpc, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null]
10.5.2020 14:55:58	[Week, AccountDescriptiveName, CampaignName, Cost, Impressions, Interactions, Clicks, AverageCpc, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null, 2020-04-27, null, null, null, null, null, null, null] But I am glad, my problem is not solved easily, that kind of reassures me. Regards, Stella

drag_handle

[Google Ads Scripts Forum Advisor]

Hi Stella,

 

Thank you for getting back to us. I work with Ejay and Matt and allow me to provide support on this.

 

I made updates on Matt's previous code snippet. This time, I'm using sheets' .setValues function to append existing data. You can try the following code below:

var report = AdsApp.report(query);
  var rows = report.rows();
 
  while(rows.hasNext()) {
    var row = rows.next();
    var rowContents = [];
    var arrayOfColumns = columns.slice(0,columns.length);
    Logger.log(arrayOfColumns);
    arrayOfColumns.forEach(function(val){
      rowContents.push(row[val]);
    });
    Logger.log("rowContents: "+JSON.stringify(rowContents));
    sheet.getRange(sheet.getLastRow() + 1, 1, 1, arrayOfColumns.length).setValues([rowContents])
    SpreadsheetApp.flush();
  }

Hope this helps.

Regards,

Markie