Posts tagged with google-sheets

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(); }

When I run an ads-script to call the app-script::spreadsheet API, I get the following error:

var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL); var rangeValues = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues(); ==> Cannot find method getRange(number,number,number,number)

How can it be? Only a subset of the app-script sheet api is available from ads-script?

  1. When I run an ads-script to call the "spreadsheet" API. I want to enrich a sheet by duplicating some rows according to values I have in a script.

How should I do this effectively?

only by creating a new tab? Starting from the last row? As I change the range while iterating.

  for ( i = 0; i < lastColumn - 1; i++){     for ( j = 0 ; j < lastRow - 1; j++){       var kw_data = kw_to_label[rangeValues[j][i]];         if(kw_data != null){         sheet.getRange(j,i).setValue(kw_data.labels[k]);

An example:

dict: k1 -> val1, val2 k2 -> val3

and sheet:

I need to export campaign statistics to .xlsx, which is stored in Google Drive. I substitute the link to this document in the script, and I get this error:

"Service Spreadsheets failed while accessing document with id 1S_rjqsgbjyTKXEo14j_m2xtoIwgHCFi1. (file Code.gs, line 18)".

In the privacy parameters of the document, "editing for everyone who has the link" is configured. How do I solve the problem? I tested these two links: https://docs.google.com/spreadsheets/d/1S_rjqsgbjyTKXEo14j_m2xtoIwgHCFi1/edit#gid=1649084583/ https://drive.google.com/file/d/1S_rjqsgbjyTKXEo14j_m2xtoIwgHCFi1/view?usp=sharing

Here is the script itself.

function main() {  //  var url = 'https://drive.google.com/file/d/1S_rjqsgbjyTKXEo14j_m2xtoIwgHCFi1/view?usp=sharing/';  var datasheet = 'Ali_web_all_acc_export.xlsx';  var columns = 'CampaignName, AdvertisingChannelType, Cost, Date';  var report = 'CAMPAIGN_PERFORMANCE_REPORT';  var selector = 'CampaignName DOES_NOT_CONTAIN "Шашлык15loooool"';  var during = 'YESTERDAY';  // Функция выгрузки  exportReportToSpreadsheet(url, datasheet, columns, report, selector, during); } function exportReportToSpreadsheet(url, datasheet, columns, report, selector, date) {  var spreadsheet = SpreadsheetApp.openByUrl(url);  var sheet = spreadsheet.getSheetByName(datasheet);  var report = AdWordsApp.report(    'SELECT ' + columns + ' ' +    'FROM ' + report + ' ' +    'WHERE ' + selector +' ' +    'DURING '+ date + ' '    );  report.exportToSheet(sheet); }

is there a way to easily call a GoogleAds API (TargetingIdeaService to be more precise) from Google Spreadsheet scripts? It looks like there should be an integratation of them but I'm missing it.

I can make calls to others APIs with no issues. I have registered myself on Google Ads and got a test token, which should be changed when I get the definitive one.

Thanks in advance.

  1. 1
  2. 2