Ad Performance Report Script

[Matthew Seldon]

I'm using the script from this page: https://developers.google.com/google-ads/scripts/docs/solutions/ad-performance and have got it to work but everytime the script is run it creates the data in a new sheet. 

Is it possible instead to overwrite the data in my existing sheet? If so what changes would be needed to the script.

Hoping you can help! Thanks.

[Google Ads Scripts Forum Advisor]

Hi Matthew,

Thank you for reaching out to us.

With regard to your concern, I'm afraid that the Ad Performance Report solution script is built to create the data in a new sheet. You may use the clearContent() method to clear the sheet's content at the start of the main function.

Regards,

Teejay Wennie Google Ads Scripts Team

ref:_00D1U1174p._5004Q2bDNMR:ref

[Matthew Seldon]

Hi Teejay,

I've tried adding the code in red (below) at the start of "main()" but get an error - TypeError: Cannot read property 'getSheets' of null . I'm no coder so I know I've messed this up and hope you can help further :) but I can guess that unless I also remove some additional code it won't stop a new sheet being created either.

function main() {
 
console.log(`Using template spreadsheet - ${SPREADSHEET_URL}.`);
  const spreadsheet = copySpreadsheet(SPREADSHEET_URL);
  console.log(`Generated new reporting spreadsheet ${spreadsheet.getUrl()} ` +
      `based on the template spreadsheet. ` +
      `The reporting data will be populated here.`);
 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var range = sheet.getRange("A1:D10");
range.clearContent();

[Google Ads Scripts Forum Advisor]

Hello Matthew,

I’m James, also a member of the Google Ads scripts support team. Allow me to assist you further.

Can you please provide us with the following details below so that we can further check and guide you accordingly?

• Google Ads account ID / CID
• Name of the involved script

Regards,

James Howell Google Ads Scripts Team

ref:_00D1U1174p._5004Q2bDNMR:ref

[Matthew Seldon]

Hey James,

Here are the details:

752-549-4603

Ad Performance Report - Hourly

Apprechiate any help!

Thanks.

[Google Ads Scripts Forum Advisor]

Hello Matthew,

Thank you for providing the requested information. I made some adjustments to your script in order for it to overwrite data in an existing spreadsheet. That said, can you please try to implement the attached script below and let me know how it goes on your end?

Regards,

[Google Ads Scripts Forum Advisor]

[Matthew Seldon]

Amazing, it works perfectly, thanks so much for your help James!

[Google Ads Scripts Forum Advisor]

Hello Matthew,

I’m glad that it works perfectly on your end. If you need anything else from us, please let us know. We’re here to assist you.

Regards,

[Matthew Seldon]

One more quick question!

How do I stop the emails getting sent to myself everytime the script runs?

// Array of recipient emails. Comment out to not send any emails.
    'recipient_emails': [''],

I've tried leaving the field blank as above but I get a script error when doing it this way.

[Google Ads Scripts Forum Advisor]

Hi Matthew,

Please comment out the recipient_emails line:

    // Array of recipient emails. Comment out to not send any emails.

    // 'recipient_emails': ['INSERT_EMAIL_HERE'],
    // URL of the default spreadsheet template. This should be a copy of

The '//' denotes a comment, so that line will be ignored on execution.

Regards,

Matt Google Ads Scripts Team

ref:_00D1U1174p._5004Q2bDNMR:ref

[Matthew Seldon]

Hey guys,

Hoping for a little more help here as I've just realised with this new update to the script as I'm using the same sheet each when the script reruns it doesn't clear rown further down if they are no longer used.

Is there a way to update the script so that each time it runs it clears the dynamic rows (rows populated with data from Google) on the tab "Final URL" before repopulating?

[Google Ads Scripts Forum Advisor]

Hello Matthew,

Thank you for getting back to us.

I believe that you can use the clearContent() method to clear the data of a specific rows in your sheet. Can you please try to implement the said method and let me know how it goes on your end?

Regards,

James Howell Google Ads Scripts Team

ref:_00D1U1174p._5004Q2bDNMR:ref

[Matthew Seldon]

Perfect! Got it working, thank you :D

[Google Ads Scripts Forum Advisor]

Hi Matthew,
 

Thank you for the update.
 

Our team is happy to hear that. If you have further questions / concerns for our team, please feel free to get back to us. We will be happy to address it.

Regards,

Darwin Google Ads Scripts Team

ref:_00D1U1174p._5004Q2bDNMR:ref