Google Ads Scripts: Can't Create A Blob Of CSV String. Unexpected error while getting the method or property newBlob on object Utilities
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();
When I saw your script, it seems that the function convert_sheet_to_csv returns no value. By this, csv_data of var csv_data = convert_sheet_to_csv(sheet); is undefined. I thought that this is the reason for your current issue of Exception: Unexpected error while getting the method or property newBlob on object Utilities.. In this case, how about the following modification?
Modified script:In this modification, convert_sheet_to_csv is modified as follows.
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'; } return csv_data; // Added } By this, csv_data of var csv_data = convert_sheet_to_csv(sheet); has the string value. And, var blob = Utilities.newBlob(csv_data, mime, filename); works.Accepting this solution. Implementation worked and I have received the email I expected.
Answering my own question as I've further optimized the code and it no longer requires a Google Sheet. Now, it can also take more than one recipient.
// Report settings const REPORT_NAME = 'GCLID Report'; const EMAIL_TO = ['user@email.org']; const EMAIL_FROM = 'Automatic Emailer Script'; const EMAIL_SUBJECT = 'Your Google Ads Report Is Ready: ' + REPORT_NAME; const EMAIL_BODY = 'Attached is the ' + REPORT_NAME; // File settings const YESTERDAY_STR = calculateYesterday(); const FILE_MIME_TYPE = 'text/plain'; const FILENAME = 'gclid_report_' + YESTERDAY_STR + '.csv'; const REPORT_QUERY = ` SELECT click_view.gclid, segments.date, customer.id, ad_group.id, campaign.id FROM click_view WHERE segments.date DURING YESTERDAY ` // Collects the report for the previous day function collectReport() { return AdsApp.report(REPORT_QUERY); } // Calculates yesterday's date string function calculateYesterday() { const millis_per_day = 1000 * 60 * 60 * 24; const now = new Date(); const yesterday = new Date(now.getTime() - millis_per_day); const timeZone = AdsApp.currentAccount().getTimeZone(); return Utilities.formatDate(yesterday, timeZone, 'yyyy-MM-dd'); } // Converts report rows into CSV format function reportToCsv(report) { const rows = report.rows(); const csvRows = []; // Creates CSV headers const first_row = rows.next(); const columns = Object.keys(first_row).filter(column => column !== 'formatForUpload'); // Creates CSV first row const values = columns.map(column => first_row[column]); csvRows.push(values.join(',')); // Creates CSV remaining rows while (rows.hasNext()) { const row = rows.next(); const values = columns.map(column => row[column]); csvRows.push(values.join(',')); } return columns.join(',') + '\r\n' + csvRows.join('\r\n'); } // Sends an email with the report attached function main() { const report = collectReport(); const csvData = reportToCsv(report); const blob = Utilities.newBlob(csvData, FILE_MIME_TYPE, FILENAME); MailApp.sendEmail({ to: EMAIL_TO.join(','), subject: EMAIL_SUBJECT, body: EMAIL_BODY, name: EMAIL_FROM, attachments: [blob], }); }