Creating a Dashboard for multiple accounts

[Jan Hahlbrock]

Hello Advisors,

I already got so much help in this forum and maybe you guys can help me once more.

I am currently creating a Dashboard for multiple google ads accounts and am running into multiple problems.

The general idea is, that for each customer that holds a specific label there will be a number of charts created in a spreadsheet.

My current approach is to get all the reports for the accounts in parallel and merge them (sequentially) into an Array, then I want to create the charts(sequetially) from this array.

My current problem is, that I dont know how to merge all the reports in the array. I think I have to use a callback function once the report is pulled, because I want to have all charts in on sheet next to each other, so they have to be prcessed in sequence (correct me if i am wrong)

my current approach looks like this:

var SCRIPT_LABEL = 'LABEL';

function run() {

var Spreadsheet_URL = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';  

var ss = SpreadsheetApp.openByUrl(Spreadsheet_URL);

var report = getReportforAccount();

  

  

}

// this will execute your script sequentially accounts and is only used for accounts in excess of 50

function executeInSequence(sequentialIds, executeSequentiallyFunc) {

    sequentialIds.forEach(function (accountId) {

        var account = MccApp.accounts().withIds([accountId]).get().next();

        MccApp.select(account);

        executeSequentiallyFunc();

    });

}

// out custom main function responsible for executing the run function

function main() {

    try {

        var accountSelector = MccApp.accounts().orderBy('Name');

        if (SCRIPT_LABEL) {

            accountSelector = accountSelector.withCondition("LabelNames CONTAINS '" + SCRIPT_LABEL + "'");

        }

        var accountIterator = accountSelector.get();

        var accountIds = [];

        while (accountIterator.hasNext()) {

            var account = accountIterator.next();

            accountIds.push(account.getCustomerId());

        }

        var parallelIds = accountIds.slice(0, 50);

        var sequentialIds = accountIds.slice(50);

        MccApp.accounts()

            .withIds(parallelIds)

            .executeInParallel('run');

        if (sequentialIds.length > 0) {

            executeInSequence(sequentialIds, run);

        }

    }

    catch (exception) {

        Logger.log('Running on non-MCC account.');

        run();

    }

}

function insertCharts(reportArray,chartSheet){

  //TODO create charts from report and insert them into Spreadsheet

}

function getReportforAccount() {

var report = AdWordsApp.report("SELECT Date, Clicks, Conversions, Cost, AllConversionValue, Device FROM ACCOUNT_PERFORMANCE_REPORT DURING LAST_30_DAYS"); //

var rows = report.rows();

while (rows.hasNext()) {

    var row = rows.next();

    var clicks = row["Clicks"];

    var conversions = row["Conversions"];

    var cost = row["Cost"];

    var allConversionValue = row["AllConversionValue"];

    var device = row["Device"];

    var date = row["Date"];

}

  return report;

  }

My idea now is to add a callback function to run() that will merge all the results in one array, then another function that creats the charts from said array and then inserts them into the spreadsheet. Unfortunately i have not worked with callbacks and i am puzzled as where to put the callback function so that it will actually run sequentially.

Any help is greatly appreciated.

Greetings

Jan

[Google Ads Scripts Forum Advisor]

Hi Jan,

Thank you for reaching out to us. Allow me to assist you in this.

With regard to your use case, instead of merging the result into an array variable before inserting the data to the spreadsheet, you could just execute your run function every time, then utilize the appendRow(rowContents) which appends your extracted data into your spreadsheet.

Regards,

Teejay Wennie Pimentel Google Ads Scripts Team

ref:_00D1U1174p._5004Q26AeOd:ref

[Jan Hahlbrock]

Hey Teejay,

i am still working on this and have another question.

I now managed to fill the Spreadsheet with Data and i have one tab per customer with the Report inside to find the easy. The Report always has the same size, and covers the range A1:D31 in each sheet.

Now I want to create charts from all these reports in one single spreadsheet.

to make things easier to read i have seperated my function into two seperate functions. The first is responsible for pulling the reports and is fine. the second however keeps troubling me, as it keeps reporting  "Those rows are out of bounds. (file Code.gs, line 24)" 

here it is:

var Spreadsheet_URL = "xxxxxxxxxxx"

function main() {

  var ss = SpreadsheetApp.openByUrl(Spreadsheet_URL);

  var chartSheet = ss.getSheetByName("Charts");

  if(ss.getSpreadsheetLocale()!='de'){ss.setSpreadsheetLocale('de');} //ignore this, i need it to get proper values from the reports as i insert them while the locale is set to "en"

  var sheetarray = ss.getSheets();

  

  

for(var i = 0; i<sheetarray.length;i++){//walk through all the Sheets and create a chart from each

  var currentsheet = sheetarray[i];

  var sheetName = currentsheet.getName();

  

var chart = chartSheet.newChart()

  .asLineChart()

  .addRange(ss.getRange('\''+sheetName+'\'!A1:D31'))

  .setOption('useFirstColumnAsDomain', true)

  .setOption('title', sheetName)

  .setOption('height', 400)

  .setOption('width', 600)

  .setPosition(i, 6, 99, 0)

  .build();

  chartSheet.insertChart(chart);

    

  }

}

The data in each sheet consists the following 4 Columns : Date, Conversions, Cost, AllConversionValue and if possible I want to create two charts from this.

One with Date on the x-axis, the Cost on the left-y-axis and the Conversions on the right-y-axis.

The other one with date on the x-axis, Cost on the let y-axis and AllConversionValue on the right y-axis.

These two Charts should be right next to each other the following two charts should be appended at the bottom of the last two charts and so on.

Maybe you could help me with the chart builder as the number of available options seem overwhelming to me and i dont know how to choose data for the left y-axis.

Thank you for your time,

Jan

[Jan Hahlbrock]

This is where I want to get, but of course with many more charts. Kunde stands for Customer.

[Google Ads Scripts Forum Advisor]

Hi Jan,

Thank you for getting back to us. However, it appears that your concern is more on App Scripts rather than anything specific to Google Ads Script. With this, we would recommend reaching out to App Scripts Team. The developer specialist there would be able to provide a better insight regarding to your spreadsheet chart creation concern.