Looker StudioでGoogle Apps ScriptとスプレッドシートからPVと累計と目標トレンドのグラフを作成
Looker StudioでGoogle Apps ScriptとスプレッドシートからPVと累計と目標トレンドのグラフを作成
Google Apps ScriptでPVを取得。
function runReport() {
/************************************************************************/
/* 実行前に「PROPERTY_ID」「SPREADSHEET_ID」「SHEET_NAME」の情報(＊＊)を入力
constSTART_DATE = “2023-03-01”;
constEND_DATE = “2023-03-31”; この日付けは、適宜変更する。
*/
/************************************************************************/
//BASIC INFO
//取り込み対象のGoogle AnalyticsプロパティID
constPROPERTY_ID = ‘PROPERTY_ID’;
//出力先のスプレッドシートID
constSPREADSHEET_ID = “SPREADSHEET_ID”;
//出力先のシート名
constSHEET_NAME = “SHEET_NAME”;
//PARAMETERS
//取得したいメトリクスの名前
constMETRIC_NAMES = [‘screenPageViews’, ‘newUsers’, ‘activeUsers’, ‘bounceRate’];
//取得したいディメンジョンの名前
constDIMENSION_NAMES = [‘Date’];
//取得期間の開始終了
constSTART_DATE = “2023-03-01”;
constEND_DATE = “2023-03-31”;
try {
constmetrics = METRIC_NAMES.map((x) => {
consttmp = AnalyticsData.newMetric();
tmp.name = x;
returntmp;
});
constdimensions = DIMENSION_NAMES.map((x) => {
consttmp = AnalyticsData.newDimension();
tmp.name = x;
returntmp;
});
constdateRange = AnalyticsData.newDateRange();
dateRange.startDate = START_DATE;
dateRange.endDate = END_DATE;
constrequest = AnalyticsData.newRunReportRequest();
request.dimensions = dimensions;
request.metrics = metrics;
request.dateRanges = dateRange;
constreport = AnalyticsData.Properties.runReport(request,’properties/’ + PROPERTY_ID);
if (!report.rows) {
Logger.log(‘No rows returned.’);
return;
}
constspreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
constsheet = spreadsheet.getSheetByName(SHEET_NAME);
sheet.clear();
// Append the headers.
constdimensionHeaders = report.dimensionHeaders.map(
(dimensionHeader) => {
returndimensionHeader.name;
});
constmetricHeaders = report.metricHeaders.map(
(metricHeader) => {
returnmetricHeader.name;
});
constheaders = […dimensionHeaders, …metricHeaders];
sheet.appendRow(headers);
// Append the results.
constrows = report.rows.map((row) => {
constdimensionValues = row.dimensionValues.map(
(dimensionValue) => {
returndimensionValue.value;
});
constmetricValues = row.metricValues.map(
(metricValues) => {
returnmetricValues.value;
});
return […dimensionValues, …metricValues];
});
sheet.getRange(2, 1, report.rows.length, headers.length).setValues(rows);
Logger.log(‘Report spreadsheet created: %s’,spreadsheet.getUrl());
} catch (e) {
Logger.log(e);
}
}
function _runReport() {
/**
* TODO(developer): Uncomment this variable and replace with your
* Google Analytics 4 property ID before running the sample.
*/
constpropertyId = PROPERTY_ID;
try {
constmetric = AnalyticsData.newMetric();
metric.name = ‘screenPageViews’;
constdimension = AnalyticsData.newDimension();
dimension.name = ‘pageTitle’;
constdateRange = AnalyticsData.newDateRange();
dateRange.startDate = ‘today-90’;
dateRange.endDate = ‘today’;
constrequest = AnalyticsData.newRunReportRequest();
request.dimensions = [dimension];
request.metrics = [metric];
request.dateRanges = dateRange;
constreport = AnalyticsData.Properties.runReport(request,
‘properties/’ + propertyId);
if (!report.rows) {
Logger.log(‘No rows returned.’);
return;
}
constspreadsheet = SpreadsheetApp.create(‘Google Analytics Report’);
constsheet = spreadsheet.getActiveSheet();
// Append the headers.
constdimensionHeaders = report.dimensionHeaders.map(
(dimensionHeader) => {
returndimensionHeader.name;
});
constmetricHeaders = report.metricHeaders.map(
(metricHeader) => {
returnmetricHeader.name;
});
constheaders = […dimensionHeaders, …metricHeaders];
sheet.appendRow(headers);
// Append the results.
constrows = report.rows.map((row) => {
constdimensionValues = row.dimensionValues.map(
(dimensionValue) => {
returndimensionValue.value;
});
constmetricValues = row.metricValues.map(
(metricValues) => {
returnmetricValues.value;
});
return […dimensionValues, …metricValues];
});
sheet.getRange(2, 1, report.rows.length, headers.length)
.setValues(rows);
Logger.log(‘Report spreadsheet created: %s’,
spreadsheet.getUrl());
} catch (e) {
console.log(e);
// TODO (Developer) – Handle exception
Logger.log(‘Failed with error: %s’, e.error);
}
}
スプレッドシートの別シートにグラフ用のシートを作成する
Looker Studioでスプレッドシートからグラフを作成
関連記事一覧
コメント ( 0 )
トラックバックは利用できません。
この記事へのコメントはありません。