I'm using the following Google Apps Script to pull in data using the Google Ads API. The data appears to be pulling in fine, but parsing it seems to be an issue. Here is the script:

 function getGoogleAdsData() {   const fileId = 'fileID';   const campaignSheetName = 'Campaigns';   const campaignMetrics = ['segments.date', 'campaign.id', 'metrics.cost_micros'];   // Calculate the dates for the previous week (Monday to Sunday)   const today = new Date();   const dayOfWeek = today.getDay(); // 0 (Sunday) to 6 (Saturday)      // Adjust today to get the last Sunday   const lastSunday = new Date(today);   lastSunday.setDate(today.getDate() - dayOfWeek - 0);   // Calculate the previous Monday (6 days before last Sunday)   const lastMonday = new Date(lastSunday);   lastMonday.setDate(lastSunday.getDate() - 6);   const formattedStartDate = Utilities.formatDate(lastMonday, Session.getScriptTimeZone(), 'yyyy-MM-dd');   const formattedEndDate = Utilities.formatDate(lastSunday, Session.getScriptTimeZone(), 'yyyy-MM-dd');      Logger.log(`Fetching data from ${formattedStartDate} to ${formattedEndDate}`);   // Fetch Campaign Data   const campaignData = fetchGoogleAdsData(formattedStartDate, formattedEndDate, campaignMetrics);   const formattedCampaignData = campaignData.map(row => [     formatDate(row[0]), // Date     row[1], // Campaign ID     row[2] // Cost in currency units   ]);      // Write to Sheets if there's data   if (formattedCampaignData.length > 0) {     writeToSheet(fileId, campaignSheetName, formattedCampaignData, ['Date', 'Campaign ID', 'Cost']);   } else {     Logger.log('No data available to write to the sheet.');   } } function fetchGoogleAdsData(startDate, endDate, metrics) {   const query = `     SELECT ${metrics.join(', ')}     FROM campaign     WHERE segments.date BETWEEN '${startDate}' AND '${endDate}'   `;      const apiUrl = 'https://googleads.googleapis.com/v17/customers/xxxxxxxx/googleAds:searchStream';   const options = {     method: 'POST',     headers: {       'Authorization': `Bearer ${ScriptApp.getOAuthToken()}`,        'developer-token': 'OUR-TOKEN',       'login-customer-id': 'xxxxxxxxxx'     },     contentType: 'application/json',     payload: JSON.stringify({ query: query }),     muteHttpExceptions: true   };      const response = UrlFetchApp.fetch(apiUrl, options);   const responseData = JSON.parse(response.getContentText());   Logger.log(`Full API Response: ${JSON.stringify(responseData)}`);   if (responseData.length > 0 && responseData[0].results) {     Logger.log(`Found ${responseData[0].results.length} result(s).`);     return responseData[0].results.map(result => {       Logger.log(`Metrics object: ${JSON.stringify(result.metrics)}`);       const row = [];       metrics.forEach(metric => {         let value = getNestedValue(result, metric.split('.'));         Logger.log(`Metric: ${metric}, Value: ${value}`); // Log each metric's value         if (metric === 'metrics.cost_micros') {           value = value / 10; // Convert micros to currency units           Logger.log(`Converted cost_micros: ${value}`);         }         row.push(value);       });       return row;     });   } else {     Logger.log('No results found in response or response structure is different.');     return [];   } } function getNestedValue(obj, keys) {   return keys.reduce((acc, key) => (acc && acc[key] !== undefined) ? acc[key] : null, obj); } function writeToSheet(fileId, sheetName, data, headers) {   const sheet = SpreadsheetApp.openById(fileId).getSheetByName(sheetName);   sheet.clear(); // Clear existing data   sheet.appendRow(headers); // Add headers   if (data.length > 0 && data[0].length > 0) {     sheet.getRange(2, 1, data.length, data[0].length).setValues(data); // Add data   } else {     Logger.log('No data to write to the sheet.');   } } function formatDate(dateString) {   const date = new Date(dateString);   return Utilities.formatDate(date, Session.getScriptTimeZone(), 'dd/MM/yyyy'); } 

The full API response looks something like this:

Full API Response: [{"results":[{"campaign":{"resourceName":"customers/xxxxx/campaigns/3509213","id":"3509213"},"metrics":{"costMicros":"41060000"},"segments":{"date":"2024-08-26"}},{"campaign":{"resourceName":"customers/xxxxx/campaigns/3509213","id":"3509213"},"metrics":{"costMicros":"54460000"},"segments":{"date":"2024-08-27"}},{"campaign":{"resourceName":"customers/xxxxx/campaigns/3509213","id":"3509213"},"metrics":{"costMicros":"33690000"},"segments":{"date":"2024-08-28"}}..... 

And further logging shows this:

8:05:38 AM  Info    Found 232 result(s). 8:05:38 AM  Info    Metrics object: {"costMicros":"41060000"} 8:05:38 AM  Info    Metric: segments.date, Value: 2024-08-26 8:05:38 AM  Info    Metric: campaign.id, Value: 3509213 8:05:38 AM  Info    Metric: metrics.cost_micros, Value: null 8:05:38 AM  Info    Converted cost_micros: 0 

I've tried changing the metrics.cost_micros to metrics.costMicros, but this doesn't work and returns an unrecognised field.

Why is 'Metric: metrics.cost_micros, Value: null'?

I am creating facebook video ads with google app script, for this first I have to upload video with this endpoint: "https://graph-video.facebook.com/v19.0/pageId/videos". complete code function is this:

function uploadVideoChunk(pageId, uploadSessionId, startOffset, videoChunk) {     //return uploadFileFetch(pageId, uploadSessionId, startOffset, videoChunk)     var url = "https://graph-video.facebook.com/v19.0/" + pageId + "/videos";     var payload = {       "upload_phase": "transfer",       "access_token": page_access_token,       "upload_session_id": uploadSessionId,       "start_offset": startOffset,       "video_file_chunk": videoChunk     };     var options = {       "method": "POST",       "payload": payload,       "muteHttpExceptions": true     };     var response = UrlFetchApp.fetch(url, options);     var responseData = JSON.parse(response.getContentText());     console.log('upload Video Chunk responseData = ',responseData)     var newStartOffset = responseData.start_offset;          return newStartOffset; } 

I am getting this error: Your video upload timed out before it could be completed. This is probably because of a slow network connection or because the video you're trying to upload is too large. Please try again

could you please help how can I solve this.

Thank you

I am following this: https://developers.facebook.com/docs/video-api/guides/publishing/#step-2--upload-chunks-individually

How can I optimize the following script? It is timing out due to probably multiple loops. The loops are in order:

1-Loop through all Google Ads accounts.

2-Loop through all Ad Groups.

3-Within each Ad Group, check if any ads have a "DISAPPROVED" status. If yes, append the data to Google Sheets.

  function main() {   var sheetId = 'XXX';   var sheetName = 'XXX';   var spreadsheet = SpreadsheetApp.openById(sheetId);   var sheet = spreadsheet.getSheetByName(sheetName);   if (!sheet) {     sheet = spreadsheet.insertSheet(sheetName);   } else {     // Clear the existing contents     sheet.clearContents();   }   // Adding headers (column names)   var headers = ['Account Name', 'Campaign', 'Ad Group'];   sheet.appendRow(headers);    // Iterating through each client account   var accountIterator = AdsManagerApp.accounts().get();   while (accountIterator.hasNext()) {     var account = accountIterator.next();     AdsManagerApp.select(account);     var adGroupIterator = AdsApp.adGroups()         .withCondition('CampaignStatus = ENABLED')         .withCondition('AdGroupStatus = ENABLED')         .get();     while (adGroupIterator.hasNext()) {       var adGroup = adGroupIterator.next();       // Check if the ad group has any disapproved ads       var disapprovedAdFound = false;       var adIterator = adGroup.ads().get();       while (adIterator.hasNext()) {         var ad = adIterator.next();         if (ad.getPolicyApprovalStatus() === 'DISAPPROVED') {           disapprovedAdFound = true;           break;         }       }       if (disapprovedAdFound) { // Disapproved ads found         // Record the details of the ad group with disapproved ads         sheet.appendRow([           account.getName(),           adGroup.getCampaign().getName(),           adGroup.getName()         ]);       }     }   } } 

I've been working on this google ads script to email a csv to me. It queries the ad words api for data, writes it to a spreadsheet, reads it from the spreadsheet, iterates through the rows to create a csv string, creates a blob and sends the email.

The reason it writes/reads to a spreadsheet is to get the data in the correct format. I'm not sure there is a better way.

The problem is that it will not create the blob. It fails with this error:

Exception: Unexpected error while getting the method or property newBlob on object Utilities. (line 53) 

I've looked online and can not find much on this error. I've tried removing arguments on the newBlob statement, sending the raw csv string, etc. Nothing seems to work and I can't figure out why creating the blob fails.

const SS_ID = '1_super_secret_id_Q'; const SS_NAME = 'GCLID Report'; const SHEET_NAME = 'DURING YESTERDAY' const EMAIL_TO = 'c_super_secret_email_g' const MILLIS_PER_DAY = 1000 * 60 * 60 * 24; function collect_report() {   return AdsApp.report(     'SELECT                ' +     '  click_view.gclid,   ' +     '  segments.date,      ' +     '  customer.id,        ' +     '  ad_group.id,        ' +     '  campaign.id         ' +     'FROM click_view       ' +     'WHERE segments.date DURING YESTERDAY'   ); } function calculate_yesterday() {   var now = new Date();   var yesterday = new Date(now.getTime() - MILLIS_PER_DAY);   var timeZone = AdsApp.currentAccount().getTimeZone();   var yesterday_str = Utilities.formatDate(yesterday, timeZone, 'yyyy-MM-dd');   return yesterday_str; } function convert_sheet_to_csv(sheet) {   var csv_data = '';      // This represents ALL the data.   var range = sheet.getDataRange();   var values = range.getValues();   // This logs the spreadsheet in CSV format.   for (let i = 0; i < values.length; i++) {     csv_data += (values[i].join(',')) + '\r\n';   } } function main() {   var report = collect_report();   var spreadsheet = SpreadsheetApp.openById(SS_ID);   var sheet = spreadsheet.getSheetByName(SHEET_NAME);      sheet.clearContents();   report.exportToSheet(sheet);   var mime = 'text/plain'   var yesterday_str = calculate_yesterday();   var filename = 'gclid_report.csv';   var csv_data = convert_sheet_to_csv(sheet);   var blob = Utilities.newBlob(csv_data, mime, filename);   var email_subject = 'Your Google Ads Report Is Ready: GCLID Report';   var email_body = 'Attached is the ' + filename;   MailApp.sendEmail(     EMAIL_TO,     email_subject,     email_body,     {         name: 'Automatic Emailer Script',         attachments: [blob]     }   ); } main(); 

Hi I am trying to write a script that will extract the data from Google Ads Report (Call Details) or 'call_metrics_call_details_report' to a spreadsheet automatically. I have been able to extract all the details successfully - except for the caller phone number. There also does not seem to be a google attribute for this. Can anybody help? Thank you

function importCallDetails() {   var accountId = AdWordsApp.currentAccount().getCustomerId();   var startDate = "INSERT_START_DATE_HERE";   var endDate = "INSERT_END_DATE_HERE";   var query = "SELECT CallStartTime, CallDuration, CallerCountryCallingCode, CampaignName,     CampaignId " + "FROM CALL_METRICS_CALL_DETAILS_REPORT";   var report = AdsApp.report(query);   var data = report.rows();   var spreadsheetUrl = 'https://docs.google.com/spreadsheets/*********/';   var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);   var ss = spreadsheet.getSheetByName('PAGE1');   ss.clearContents();   report.exportToSheet(ss); }   function main() {   importCallDetails(); }