Script will not automatically run Daily

[Darius Price]

Hi Everyone,

I have a script set up at an account level which runs when I initially set it up but will not run when i set it to run daily. The next day i go into the account and script has not run and i have to manually run it. Has anyone encountered this problem before?

[Google Ads Scripts Forum Advisor]

Hello Darius,

 

I’m James from Google Ads Scripts Team. Thank you for reaching out to us.

 

I completely understand your concern. To resolve this, could you please try to recreate, reauthorize and reschedule the script to check if the result would be the same behavior of the script that won't work on a daily frequency?

 

Let me know how it goes on your end. 

 

Regards,

James Howell Abarsoza Google Ads Scripts Team

ref:_00D1U1174p._5004Q2B3jbo:ref

[Darius Price]

Hi James,

I have done this previously and the same issue is occurring. The script works perfectly fine but it won't automatically refresh. Do you think it could be something wrong with the script itself? I'll drop a copy of it below. Thanks for the response.

/**
*
* Advanced Quality Score Tracker by Clicteq
* The scripts plots Quality Score and impression weighed Quality Score on a daily basis
* It has 3 graphs to show the split by expected CTR, landing page experience and ad relevance
*
* Version: 1.3
* maintained by Clicteq
*
**/

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

//Control Panel

//URL of Google Sheet. Create a new Google sheet that you want the data to be outputted to and paste the URL between then quotation marks below
SHEETURL = 'https://docs.google.com/spreadsheets/d/1AK-q4ncICrKztsgtSxrMxqRW7HzpWlY111gjVq7pSgo/edit#gid=0'

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

function getDetailedReport(enums,metrics)
{
  //Downloads a report with QS components and calculates weighted averages. Returns a sheet-friendly row.

 var result = {}
 result = {'SearchPredictedCtr_BELOW_AVERAGE':1,'SearchPredictedCtr_AVERAGE':2,'SearchPredictedCtr_ABOVE_AVERAGE':3}
 var total = 0
 var total_impr = 0
  for(metric in metrics)
 {
  for(enum in enums)
    {
      var m = metrics[metric]
      var e = enums[enum]
  var query = "SELECT Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE %m = %e DURING YESTERDAY"
      var temp = query.replace('%m', m).replace('%e',e)
      var report = AdWordsApp.report(temp).rows()
      var i = 0
        var impr = 0
       
        while(report.hasNext())
        {row = report.next()
         i = i + 1
        var impr_part = parseInt(row['Impressions'])
        impr = impr + impr_part
        }
      result[metrics[metric] + '_' + enums[enum]] = {'impressions' : parseInt(impr), 'total' : parseInt(i)}
      total = total + i
      total_impr = total_impr + impr
    }
 }
  total = parseInt(total/3)
  total_impr = parseInt(total_impr/3)
 
var sheetRow = []  
 
for(metric in metrics)
{
  for(enum in enums)
  {
    m = metrics[metric]
    e = enums[enum]
    var single = result[m + '_' + e]
    single['total'] = (single['total']/total).toFixed(5)
    sheetRow.push(result[m+'_'+e]['total'])
    if(e == 'BELOW_AVERAGE')
    {
     var weighted = 1 * (single['impressions']/total_impr)
    }
    else if (e == 'AVERAGE')
    {
      var weighted = 2 * (single['impressions']/total_impr)
    }
    else if (e == 'ABOVE_AVERAGE')
    {
      var weighted = 3 * (single['impressions']/total_impr)
    }
   
    single['weighted'] = weighted
  }
}
 
var weightedAll = {}  
 
for(metric in metrics)
{m = metrics[metric]
  weightedAll[m] = 0
  for(enum in enums)
  {
  e = enums[enum]
  var single = result[m + '_' + e]
  weightedAll[m] = weightedAll[m] + single['weighted']
  }
  weightedAll[m] = weightedAll[m].toFixed(4)
}
var metrics_temp = ['SearchPredictedCtr','PostClickQualityScore','CreativeQualityScore']  
for(item in metrics_temp)
{
  sheetRow.push(weightedAll[metrics_temp[item]])
}
return sheetRow
}

function getTotalReport()
{
 
  //Downloads a QS report and calculates weighted average. Returns a sheet-friendly row.
 
var query = "SELECT Impressions, QualityScore FROM KEYWORDS_PERFORMANCE_REPORT WHERE HasQualityScore = TRUE DURING YESTERDAY"
var report = AdWordsApp.report(query).rows()
var result = {1:{'impressions':0,'total':0, 'weighted':0},
              2:{'impressions':0,'total':0, 'weighted':0},
              3:{'impressions':0,'total':0, 'weighted':0},
              4:{'impressions':0,'total':0, 'weighted':0},
              5:{'impressions':0,'total':0, 'weighted':0},
              6:{'impressions':0,'total':0, 'weighted':0},
              7:{'impressions':0,'total':0, 'weighted':0},
              8:{'impressions':0,'total':0, 'weighted':0},
              9:{'impressions':0,'total':0, 'weighted':0},
             10:{'impressions':0,'total':0, 'weighted':0}}
var impr = 0
var total = 0
var totalQs = 0
while(report.hasNext())
{
  var row = report.next()
  var impr_part = parseInt(row['Impressions'])
  var qs = row['QualityScore']
  result[qs]['impressions'] = result[qs]['impressions'] + impr_part
  result[qs]['total'] = result[qs]['total'] + 1
  impr = impr + impr_part
  var total = total + 1
  totalQs = totalQs + parseInt(qs)
}
var temp = []  
var keys = Object.keys(result)
var weighted = 0
for(place in keys)
{
  var key = keys[place]
  var weighted_part = (result[key]['impressions']/impr) * key
  result[key]['total'] = (result[key]['total']/total).toFixed(4)
  weighted = weighted + weighted_part
}
weighted = weighted.toFixed(2)  
var qses = ['1','2','3','4','5','6','7','8','9','10']
for(place in qses)
{
  var key = qses[place]
  temp.push(result[key]['total'])
}
 
temp.push(weighted)
temp.push((totalQs/total).toFixed(2))
 
return temp
}

function createGraphs()
{
  //Creates graphs when the script first runs
 
  var graphsData = {'ctr':{'all':'A2:C4', 'weighted':'K:K'},
                    'lp':{'all':'A5:C7', 'weighted':'L:L'},
                    'ad':{'all':'A8:C10', 'weighted':'M:M'},
                   'all':{'all':'A14:C23', 'weighted':'X:X'}}
  var titles = {'ctr':{'all':'QS expected CTR % of all keywords', 'weighted':'QS expected CTR weighted impressions'},
               'lp' : {'all':'QS Landing Page % of all keywords','weighted':'QS Landing Page weighted impressions'},
               'ad': {'all':'QS Ad Relevance % of all keywords','weighted':'QS Ad Relevance weighted impressions'},
               'all':{'all':'QS % of all keywords', 'weighted': 'QS weighted impressions'}}
  var axes = {'all_all':[0,1], 'ctr_all':[0,1], 'lp_all':[0,1], 'ad_all':[0,1], 'all_weighted':[0,10], 'ctr_weighted':[0,3], 'ad_weighted':[0,3], 'lp_weighted':[0,3]}
  var positions = [1,4,21,38]
 
  var sheet = SpreadsheetApp.openByUrl(SHEETURL).getSheetByName('dashboard')
  var data = SpreadsheetApp.openByUrl(SHEETURL).getSheetByName('data')
  var types = ['all','ctr','lp','ad']            
  var subTypes = ['all','weighted']
  var xAxis = data.getRange('A1:A')
  var dataBar = SpreadsheetApp.openByUrl(SHEETURL).getSheetByName('data-bar')
  var xAxisBar = dataBar.getRange('A1:C1')

  if(sheet.getCharts().length == 0)
  {Logger.log('Creating graphs...')}
  else
  {return}
  for(type in types)
  {
 
for(subType in subTypes)
    {
var seriesBar = dataBar.getRange(graphsData[types[type]][subTypes[subType]])
  var series = data.getRange(graphsData[types[type]][subTypes[subType]])
  if(subType != 0)
  {
  var pos = subType * 9
  }
  else
  {
  var pos = 2
  }
     
      if(subTypes[subType] == 'all')
      {
  var chart = sheet.newChart().asColumnChart()
  .setOption("vAxes",{0:{ "viewWindow": { "min": axes[types[type]+'_'+subTypes[subType]][0], "max": axes[types[type]+'_'+subTypes[subType]][1] } }})
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_ROWS)
  .addRange(xAxisBar)
  .addRange(seriesBar)
  .setPosition(positions[type],pos, 2, 2)
  .setNumHeaders(1)
  .setOption('title', titles[types[type]][subTypes[subType]])
  .setOption('legend', {'position':'bottom'})
  .build()
 
  }
      else
      {
       
    var chart = sheet.newChart().asLineChart()
  .addRange(xAxis)
  .addRange(series)
  .setPosition(positions[type],pos, 2, 2)
  .setNumHeaders(1)
  .setOption('title', titles[types[type]][subTypes[subType]])
  .setOption('legend', {'position':'bottom'})
  .setOption("vAxes",{0:{ "viewWindow": { "min": axes[types[type]+'_'+subTypes[subType]][0], "max": axes[types[type]+'_'+subTypes[subType]][1] } }})
  .build()
      }
  sheet.insertChart(chart)
    }
  }
}

function prepareSheet()
{
  //Prepares a new sheet by creating new tabs
 
  var sheet = SpreadsheetApp.openByUrl(SHEETURL)
  if(sheet.getSheetByName('dashboard') === null)
  {
    try
    {sheet.insertSheet('dashboard')}
catch(e){}
    try
    {sheet.insertSheet('data')
    sheet.getSheetByName('data').insertColumns(1,12)}
    catch(e){}
    try
    {sheet.insertSheet('data-bar')
    sheet.getSheetByName('data').insertColumnAfter(1)}
    catch(e){}
      Logger.log('Preparing sheet...')
    try
    {sheet.deleteSheet(sheet.getSheetByName('Sheet1'))}
    catch(e){}
   
    sheet.getSheetByName('dashboard').setHiddenGridlines(true)
    formatCells()
  }
 
 
}
function formatRange()

{
  //Formats decimals so they are displayed as %
 
  var sheet = SpreadsheetApp.openByUrl(SHEETURL)
  sheet.getRange('data!B2:J').setNumberFormat('0%')
  sheet.getRange('data!N2:W').setNumberFormat('0%')
  sheet.getRange('data!A2:A').setNumberFormat('dd/mm/yyyy')
}

function transpose()
{
  //Creates a transposed version of the first and last days, used for bar charts
 
 
  var sheet = SpreadsheetApp.openByUrl(SHEETURL).getSheetByName('data-bar')
  var range = sheet.getRange('A:C')
  range.clear()
  var lastRow = SpreadsheetApp.openByUrl(SHEETURL).getSheetByName('data').getLastRow()
  sheet.getRange('A1:A1').setFormula('=TRANSPOSE(data!A1:AD1)')
  sheet.getRange('B1:B1').setFormula('=TRANSPOSE(data!A2:AD2)')
  sheet.getRange('C1:C1').setFormula('=TRANSPOSE(data!A'+lastRow+':AD'+lastRow+')')
}

function formatCells()
{
  //Formats cells' size and fonts in the dashboard tab. Adds average quality scores as text
 
  var sheet = SpreadsheetApp.openByUrl(SHEETURL).getSheetByName('dashboard')
  var rows = [1]
  var cols_avg = ['Y','X']
  var cols = ['H','O']
  var avr = ['Normal average', 'Weighted average']
  for(row in rows)
  {
  sheet.setRowHeight(rows[row],75)
  sheet.setRowHeight(rows[row]+2,280)
    for(col in cols)
    {
      var _col = cols[col]
  sheet.getRange(_col+rows[row]+':'+_col+rows[row]).setValue('Average QS').setFontWeight("bold").setFontSize(24)
    sheet.getRange(_col+(rows[row]+1)+':'+_col+(rows[row]+1)).setValue(avr[col])
    sheet.getRange(_col+(rows[row]+2)+':'+_col+(rows[row]+2)).setFormula('=AVERAGE(data!' + cols_avg[col] +'2:' + cols_avg[col] + ')').setNumberFormat("0.00").setFontWeight('bold').setFontSize(24)
    sheet.getRange(_col+rows[row]+':'+_col+(rows[row]+2)).setBorder(true, false, true,  true, null, true)
 
  sheet.getRange(_col+':'+_col).setHorizontalAlignment('center').setVerticalAlignment('middle')
    }
  }
sheet.autoResizeColumn(8)
  sheet.autoResizeColumn(15)
}

function main ()
{
  //Combines all functions
prepareSheet()

 
var enums = ['BELOW_AVERAGE','AVERAGE','ABOVE_AVERAGE']
var metrics = ['SearchPredictedCtr', 'PostClickQualityScore', 'CreativeQualityScore']  
var sheet = SpreadsheetApp.openByUrl(SHEETURL).getSheetByName('data')


Logger.log('Downloading detailed quality scores...')
var detailedReport = getDetailedReport(enums,metrics)
Logger.log('Downloading combined quality scores...')
var totalReport = getTotalReport()



var header = [['Date','Expected_CTR_Below_Average','Expected_CTR_Average','Expected_CTR_Above_Average','LP_Below_Average','LP_Average','LP_Above_Average','Ad_Copy_Below_Average','Ad_Copy_Average','Ad_Copy_Above_Average', 'CTR_weighted','LP_weighted','Ad_Copy_weighted',
              '="1"','="2"','="3"','="4"','="5"','="6"','="7"','="8"','="9"','="10"','QS_weighted', 'QS_average']]
var currentHeader = sheet.getRange('A1:Y1').getValues()

if(currentHeader[0][0] == '')
{
sheet.getRange('A1:Y1').setValues(header)
}

var row = []
row.push(header)

var temp = []

var yday = Utilities.formatDate(new Date(new Date() - 1000 * 60 * 60 * 24), 'gmt', 'dd/MM/yyyy')
temp.push(yday)

for(item in detailedReport)
{
  temp.push(detailedReport[item])
}

temp = temp.concat(totalReport)
 
sheet.appendRow(temp)

createGraphs()
formatRange()
transpose()
}

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/nEf7a000000000000000000000000000000000000000000000QNMRR1006cc1XSR8Qr-T1XKYHbbPfg%40sfdc.net.

[Google Ads Scripts Forum Advisor]

Hi Darius,

 

Thanks for coming back. I am Harry, teammate of James on the Google Ads Scripts Team. Kindly provide the script name and your CID so that I could check this on our end and assist you further. You may send them here or privately via the reply to author option. If this option is not available at your end, you may send the requested information through our email (googleadsscr...@google.com) instead.

 

Thanks,

Harry Cliford Rivera Google Ads Scripts Team

ref:_00D1U1174p._5004Q2B3jbo:ref

[Darius Price]

Hi Harry,

Thanks for getting back to me, I have sent the relevant information to the email you suggested googleadsscr...@google.com. The same script has been implemented on 4 different accounts and will not automatically refresh for all 4.

Thanks for your assistance.

--

-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to adwords-scrip...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/rIUL-000000000000000000000000000000000000000000000QNOXME00eWfm9yX6QEeqwJHE4P6GtA%40sfdc.net.

[Google Ads Scripts Forum Advisor]

Hello Darius,

 

Thank you for providing the requested information.

 

It seems that you are trying to use a third party script and if you are trying to execute this at the MCC level, then this would not be possible. Upon having a look at your script executions for your single accounts, all seems to be working properly. If the issue still persists, kindly try to recreate, reauthorize and reschedule the script. If the same behavior happens after, please don't manually execute the script so we could check the results and investigate further.

 

Let me know how this goes on your end.

 

Regards,

James Howell Abarsoza Google Ads Scripts Team

ref:_00D1U1174p._5004Q2B3jbo:ref

[Darius Price]

Hi James 

I have implemented them at an account level as I realised they would not work at an MCC level. I have tried multiple times to reinsert the script and see if would run automatically but this has not been the case. 

Tomorrow could you please kindly check if it refreshes automatically as I have just manually refreshed them now at 13.40 when it is set to refresh automatically at 08.00 GMT. Let me know what you see tomorrow and please kindly confirm receipt that this would be looked into tomorrow (04/02/2021) as well, just for confirmation.

Many Thanks,

--

-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to adwords-scrip...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/Bsx8d000000000000000000000000000000000000000000000QNXXAU00vV2zTiC2Rayxgesj07BVkw%40sfdc.net.

[Google Ads Scripts Forum Advisor]

Hello,

 

Thank you for your reply.

 

Unfortunately, our team does not have the capability to monitor accounts. Upon checking, your script should be working as expected. In any case, please do try to recreate the script, reauthorize and reschedule. If the issue still persists upon scheduling (Daily), then please let us know (hold on manually executing the script) so we can continue our investigation on your issue.