Script for uploading STR in Excel Sheet

[Austin Goo]

I need help creating a script that uploads a Search Terms Report data into an Excel Sheet. Here is what I have so far:

function main() {

  

  //writes columnheaders in Excel sheet

  

  var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1tQ4HxrrUtO9eLBgRo7IJ9OPhPSjQoqSADEqoT7Hisd0/edit#gid=1682184925';

  var SHEET_NAME = 'output';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

  var sheet = ss.getSheetByName(SHEET_NAME);

  

  sheet.clearContents();

  sheet.appendRow(['Search Term', 'AdGroup Id', 'AdGroup Name', 'Campaign Id', 'Campaign Name', 'Triggered Keyword', 'Impressions', 'Clicks', 'Cost', 'Conversions']);

  

  

  

  //gathers data from cid and stores data in array

  

  var account = AdsManagerApp.accounts()

      .withIds(['234-254-4180'])

      .get();

  var IMPRESSIONS_THRESHOLD = 0;

  

  var report = AdWordsApp.report("SELECT Query, AdGroupId, AdGroupName, CampaignId, CampaignName, KeywordTextMatchingQuery, Impressions, Clicks, Cost, Conversions" +

   " FROM SEARCH_QUERY_PERFORMANCE_REPORT " +

   " WHERE " +

       "CampaignId IN " + account +

       " AND Impressions > " + IMPRESSIONS_THRESHOLD +

       " AND CampaignName CONTAINS_IGNORE_CASE" + "shop" +

   " DURING LAST_30_DAYS");

  

  var rowIterator = report.rows();

  var queries = {};

  var rowHolder = [];

  while (rowIterator.hasNext()) {

    

        var row = rowIterator.next();

        var query = row["Query"];

        if (!queries[query]) {

            queries[query] = 0;

        }

        queries[query]++;

        var outputRow = [];

        for (var j = 0; j < output[0].length; j++) {

            outputRow.push(row[output[0][j]]);

        }

        rowHolder.push(outputRow);

  }

  

    

    

  //writes STR data into Excel sheet

  

  spreadsheet.getRange(1, 1, output.length, output[0].length).setValues(output);

    

    

}

[Google Ads Scripts Forum Advisor]

Hi there,

Thank you for reaching out to us. Let me do the best I can to assist you in this.

For us to help you further, could you kindly provide the following details?

• CID where the script is implemented
• Script Name
• Error that you're getting when running the script
• Shareable link of the spreadsheet being used in the script (if any)

Regards,

Teejay Wennie Pimentel Google Ads Scripts Team

ref:_00D1U1174p._5004Q2B2IXR:ref

[Nils Rooijmans]

Hi,

this question pops up frequently so I decided to write a blogpost about it  (link below).

You basically have to go through these steps

1.  Connect Google Ads to the Google Sheet 

2. Create an Array to Store the Data

3. Collect the data You Need from Google Ads

4. Add the Collected Data into the Array

5.  send the Array Data to the Google Sheet

Looks like you are well on your way, however the way you implemented step 4 can be improved.

Inside your while loop, add the report row to the output array by 

- first creating an array that holds all the column names from your select clause that you want to include in your sheet,

- second, add that array to your output array (your output array being an array of arrays).

After iterating over all your report rows likes this, you can go to step 5 and add the output array to your Google sheet.

Here's the link to the blog post with example code:

https://nilsrooijmans.com/export-google-ads-data-to-google-sheets-using-scripts/

Hope this helps, 

Nils Rooijmans 

https://nilsrooijmans.com 

See my Google Ads Scripts FAQ to avoid the same mistakes I made: https://nilsrooijmans.com/google-ads-scripts-faq/

[Google Ads Scripts Forum Advisor]

Hi Nils,

Thank you for extending your knowledge to this Google Ads Scripts community.

Let us know if you have any questions/concerns related to Google Ads Scripts. We'll be happy to assist you.

Regards,

Mark Kevin Albios Google Ads Scripts Team

ref:_00D1U1174p._5004Q2B2IXR:ref

[Sigurd Fabrin]

There seems to be a few issues with the query as well.

Also, this seems somewhat overthought. If you just wish to export a report, there's no reason to jump through all those hoops. Just grab the report and export it :)

Here's a quick and easy way to export a report to Google Sheets - Not Excel, that is a (similar but) different beast entirely.

var settings = {

  url: 'https://docs.google.com/spreadsheets/d/etc',

  sheetName: 'Sheet1',

}

function main() {

  var ss = SpreadsheetApp.openByUrl(settings.url);

  var sheet = ss.getSheetByName(settings.sheetName);

  

  var report = AdsApp.report( // I haven't tested this query - comments are from a superficial quick look

   "SELECT Query, AdGroupId, AdGroupName, CampaignId, CampaignName, KeywordTextMatchingQuery, Impressions, Clicks, Cost, Conversions" +

   " FROM SEARCH_QUERY_PERFORMANCE_REPORT " +

   " WHERE " +

   "CampaignId IN " + account + // This looks wrong. Should be an array of campaignIds

   " AND Impressions > " + IMPRESSIONS_THRESHOLD +

   " AND CampaignName CONTAINS_IGNORE_CASE" + "shop" + // No need to break this in two parts. Also, be mindful of spacing

   " DURING LAST_30_DAYS");

  

report.exportToSheet(sheet);  // If you don't change anything you can just export it straightaway

}