[GAS] Retrieve Google Ads operational data in a spreadsheet by itself.

2022年11月26日

This is a way to get Google Ads (Google Ads) operational data in a spreadsheet by yourself. Various data such as impressions, clicks, and conversions can be obtained.

Click here for yahoo ads

Spreadsheet Preparation

Prepare spreadsheets. Create “GSNdb" and “GSNtemp" sheets.

Data is stored in “GSNdb". GSNtemp" is a sheet that temporarily records data, so there is no problem if it is hidden.

Google Ads Script Settings

Create a script for Google Ads. Select the account for which you want to retrieve data and click

Select “Tools & Settings" > “Scripts

　Create a new script.

Script

Use the following code

function main() {
    //データシートのファイルIDを設定
    const FileID = '1HgsixU********'

    const temp_sheet = SpreadsheetApp.openById(FileID).getSheetByName('GSNtemp');
    const db_sheet = SpreadsheetApp.openById(FileID).getSheetByName('GSNdb');
    //取得したいデータを指定
    var report = AdsApp.report('select CampaignName, Date,Clicks,Conversions,Cost,Impressions ' + 'from LANDING_PAGE_REPORT ' + 'during YESTERDAY');
    report.exportToSheet(temp_sheet);
    if (temp_sheet.getLastRow() == 1) {
        return;
    }
    const values = temp_sheet.getRange(2, 1, temp_sheet.getLastRow() - 1, temp_sheet.getLastColumn()).getValues();
    const ranges = db_sheet.getRange(db_sheet.getLastRow() + 1, 1, values.length, values[0].length);
    ranges.setValues(values);
}

Enter the ID of the spreadsheet you created on the third line.

Setting of acquired data

Specify the data to be retrieved here in line 8.

var report = AdsApp.report('select CampaignName, Date,Clicks,Conversions,Cost,Impressions ' + 'from LANDING_PAGE_REPORT ' + 'during YESTERDAY');

In this case, the previous day’s data is used to obtain the campaign name, date, number of clicks, number of conversions, operating costs, and impressions.

For more information on AdsApp, please see below.

https://developers.google.com/adwords/api/docs/appendix/reports/landing-page-report

Save the file and run it to make sure there are no errors. Approval screen will appear.

Self-acting execution settings

Return to the Script List screen and set how often you want to run the script.

This completes the process. The script will be executed at the set time and recorded in the spreadsheet.

Reference: Code to obtain the number of conversions

8行目を下記に変更するとカスタムコンバージョン別のデータが取得できます。

var report = AdsApp.report('SELECT campaign.name, segments.conversion_action_name, segments.date, metrics.all_conversions FROM campaign WHERE segments.date DURING LAST_30_DAYS'); 

参考になれば幸いです。

Related Pages

【Google広告】MCCのすべてのアカウントから運用データを取得する