弊社の管理している取引先のGoogleの請求先のデータを取得してスクリプトに出力したい

小野聡子

unread,

Oct 12, 2023, 7:44:01 AM10/12/23

to Google Ads Scripts Forum

お世話になっております。

広告代理店の会社の者です。以下教えて頂けないでしょうか。

◆やりたいこと

　私どもが管理しているGoogleの請求先一覧の情報取得しスプレッドシートへ自動的に毎日出力をしたい

◆理由

　取引先の売上データと請求データと照合させたり、GoogleAppsScriptなどを使って売り上げ集計の数式を設定するため。

◆MCCのエディタで以下のコードを実行しましたがエラーとなります。

-------------------------------------------------------------------------------------------------------------------------

// スプレッドシートのURLを指定
var SHEET_URL = 'https://docs.google.com/spreadsheets/d/----spreadSheetID-------------/edit';

// スプレッドシートのシート名を指定
var SHEET_NAME = '---SheetName----';

function main() {
// スプレッドシートを取得
var ss = SpreadsheetApp.openByUrl(SHEET_URL);
var sheet = ss.getSheetByName(SHEET_NAME);

// 必要な列ヘッダーを指定
var headers = ['アカウント名', 'お客様ID', 'お支払いプロファイルID', 'お支払いのお客様名', 'お支払いアカウントの名前', 'お支払いアカウント番号', 'お支払い元アカウント', 'お支払い元アカウントID', 'お支払い設定', 'アカウントステータス', 'サービス契約', '注文番号', '開始日', '終了日'];
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

// MCC内の子アカウントを特定
var childAccounts = AdsManagerApp.accounts().get();

// 各子アカウントから情報を取得
while (childAccounts.hasNext()) {
var childAccount = childAccounts.next();
var customerId = childAccount.getCustomerId(); // お客様IDを取得
var query = "SELECT AccountDescriptiveName, BillingProfileId, CustomerName, AccountName, AccountId, PaymentAccount, PaymentProfileId, PaymentSetting, AccountStatus, ServiceContract, OrderId, StartDate, EndDate FROM ACCOUNT_PERFORMANCE_REPORT";

// ここで AdsApp.report を実行
var report = AdsApp.report(query, { apiVersion: 'v201809' }, { childAccount: childAccount });
var rows = report.rows();

while (rows.hasNext()) {
var row = rows.next();
var rowData = [
row['AccountDescriptiveName'],
customerId, // お客様IDを設定
row['BillingProfileId'],
row['CustomerName'],
row['AccountName'],
row['AccountId'],
row['PaymentAccount'],
row['PaymentProfileId'],
row['PaymentSetting'],
row['AccountStatus'],
row['ServiceContract'],
row['OrderId'],
row['StartDate'],
row['EndDate']
];
sheet.appendRow(rowData);
}
}
}

-------------------------------------------------------------------------------------------------------------------------

◆エラー内容（BillingProfileId　以外の項目もエラーになります）

Ga: 'BillingProfileId' is not a valid field in ACCOUNT_PERFORMANCE_REPORT. Please check your spelling and casing. at main (Code:27:25)

　どうかご返信をお願いいたします。

Google Ads Scripts Forum Advisor

unread,

Oct 12, 2023, 10:09:22 AM10/12/23

to adwords...@googlegroups.com

Hi,

Thank you for reaching out to the Google Ads Scripts support team.

We would like to inform you that this Google Ads Scripts team officially offers support in only English. It would be best to reply in English if you can. If not, we will try to use Google Translate to support you as best as we can.

After reviewing your query, I understand that your concern is related to the Google Billing List.
I have tried to execute the script which you have provided and I would like to inform you that we have received the “ReferenceError: SHEET_URL is not defined” error. Could you please provide us with the following details to further investigate your issue:

Google Ads account ID / CID
Screenshot of the issue
If the script uses a spreadsheet, please provide a shareable link to the spreadsheet. You can follow this guide to share a file publicly.

This message is in relation to case "ref:_00D1U1174p._5004Q2pR3Fs:ref"

Thanks,

Google Ads Scripts Team

小野聡子

unread,

Oct 12, 2023, 11:26:29 AM10/12/23

to Google Ads Scripts Forum

Good evening
Thank you for your reply

　　・Google Ads account ID / CID➔　979-442-7825

　　・Screenshot of the issue➔I have attached

　　・If the script uses a spreadsheet, please provide a shareable link to the spreadsheet. You can follow this guide to share a file publicly.

　　　Here it is

　　　➔　https://docs.google.com/spreadsheets/d/1gQhVK8g7pgyn79_ATFnXjLrMHiLV6xV-Uw68Gm58dAg/edit#gid=295084887

ーI translated the previous sentence.ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

We become indebted to.
I am from an advertising agency company. Could you please tell me the following?

◆What I want to do
We would like to obtain information on the Google billing list that we manage and automatically output it to a spreadsheet every day.

◆Reason
To match customer sales data with billing data, and to set formulas for sales aggregation using Google AppsScript, etc.

◆I executed the code below in the MCC editor, but an error occurred.

-------------------------------------------------------------------------------------------------------------------------

// Specify spreadsheet URL

var SHEET_URL = 'https://docs.google.com/spreadsheets/d/----spreadSheetID-------------/edit';

// Specify spreadsheet sheet name

var SHEET_NAME = '---SheetName----';

function main() {

// get spreadsheet

var ss = SpreadsheetApp.openByUrl(SHEET_URL);
var sheet = ss.getSheetByName(SHEET_NAME);

// Specify required column headers
var headers = ['Account Name', 'Customer ID', 'Payment Profile ID', 'Paying Customer Name', 'Payment Account Name', 'Payment Account Number', 'Payment Account', 'Payment Original Account ID', 'Payment Settings', 'Account Status', 'Service Agreement', 'Order Number', 'Start Date', 'End Date'];

sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

// Identify child accounts within MCC
var childAccounts = AdsManagerApp.accounts().get();

// Get information from each child account

while (childAccounts.hasNext()) {
var childAccount = childAccounts.next();
var customerId = childAccount.getCustomerId(); // お客様IDを取得
var query = "SELECT AccountDescriptiveName, BillingProfileId, CustomerName, AccountName, AccountId, PaymentAccount, PaymentProfileId, PaymentSetting, AccountStatus, ServiceContract, OrderId, StartDate, EndDate FROM ACCOUNT_PERFORMANCE_REPORT";

// Now run AdsApp.report

-------------------------------------------------------------------------------------------------------------------------

◆Error details (items other than BillingProfileId will also result in an error)

Ga: 'BillingProfileId' is not a valid field in ACCOUNT_PERFORMANCE_REPORT. Please check your spelling and casing. at main (Code:27:25)