라이나생명 다이렉트 CM 키워드 리포트

function doGet(e) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets(); var result = {}; sheets.forEach(function(sheet) { var name = sheet.getName(); result[name] = sheet.getDataRange().getValues(); }); return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON); }

/** * 라이나생명 키워드 대시보드 - BigQuery 초고속 안정 연동 API * Project ID: lina-cm-keyword-dashboard * Dataset: keyword_analytics * Table: keyword_report_dashboard (또는 keyword_report_all) */ const PROJECT_ID = 'lina-cm-keyword-dashboard'; const DATASET_ID = 'keyword_analytics'; const TABLE_ID = 'keyword_report_dashboard'; const LOCATION = 'asia-northeast3'; function doGet(e) { try { const limit = (e && e.parameter && e.parameter.limit) ? parseInt(e.parameter.limit, 10) : 20000; const offset = (e && e.parameter && e.parameter.offset) ? parseInt(e.parameter.offset, 10) : 0; // 필수 컬럼 정밀 쿼리 및 페이징 const query = ` SELECT \`매체\`, \`월\`, \`주차\`, \`날짜\`, \`키워드검색어\`, \`디바이스\`, \`노출수\`, \`클릭수\`, \`광고비용\`, \`계산\`, \`가입시작\`, \`결제\`, \`광고 영역\`, \`상품명\`, \`상품군\` FROM \`${PROJECT_ID}.${DATASET_ID}.${TABLE_ID}\` LIMIT ${limit} OFFSET ${offset} `; const request = { query: query, useLegacySql: false, timeoutMs: 25000, location: LOCATION }; const queryResults = BigQuery.Jobs.query(request, PROJECT_ID); if (!queryResults.jobComplete) { throw new Error('BigQuery 쿼리 실행 시간이 초과되었습니다.'); } const fields = (queryResults.schema && queryResults.schema.fields) ? queryResults.schema.fields : []; const headers = fields.map(f => f.name); const rawRows = queryResults.rows || []; const compactRows = rawRows.map(r => { return r.f ? r.f.map(c => c.v) : []; }); return ContentService.createTextOutput(JSON.stringify({ status: 'success', totalRows: compactRows.length, offset: offset, limit: limit, dataset: DATASET_ID, table: TABLE_ID, location: LOCATION, headers: headers, rows: compactRows })).setMimeType(ContentService.MimeType.JSON); } catch(err) { return ContentService.createTextOutput(JSON.stringify({ status: 'error', message: err.toString() })).setMimeType(ContentService.MimeType.JSON); } } function testQuery() { const res = doGet(); Logger.log(res.getContent().substring(0, 500)); }