933 lines
24 KiB
TypeScript
933 lines
24 KiB
TypeScript
import { mkdir } from "node:fs/promises";
|
|
import { HyundaiCustomerVerifier } from "../hyundai/hyundaiCustomerVerifier";
|
|
import { take } from "es-toolkit";
|
|
|
|
const GOOGLE_AUTH_SCOPE = "https://www.googleapis.com/auth/spreadsheets";
|
|
const TOKEN_PATH = ".tokens/google-oauth-token.json";
|
|
const SHEET_URLS_PATH = "google/sheetUrls.txt";
|
|
const DEFAULT_REDIRECT_PORT = 3487;
|
|
const CALLBACK_PATH = "/oauth2callback";
|
|
const DEFAULT_TARGET_SHEET_URL =
|
|
"https://docs.google.com/spreadsheets/d/1LGF93SGkrIqqniY-a3ReTPhizsEMtgm5QBC8tSALmVM/edit?gid=0#gid=0";
|
|
const TARGET_HEADERS = [
|
|
"id",
|
|
"created_time",
|
|
"campaign_name",
|
|
"form_name",
|
|
"platform",
|
|
"full_name",
|
|
"phone_number",
|
|
"site",
|
|
"created_at",
|
|
"activation_channel",
|
|
"customer_group",
|
|
"sales_point",
|
|
"contact_number",
|
|
"primary_product",
|
|
"status",
|
|
"key",
|
|
] as const;
|
|
|
|
type TokenResponse = {
|
|
access_token: string;
|
|
expires_in: number;
|
|
refresh_token?: string;
|
|
scope: string;
|
|
token_type: string;
|
|
};
|
|
|
|
type SavedToken = {
|
|
access_token: string;
|
|
expiry_date: number;
|
|
refresh_token: string;
|
|
scope: string;
|
|
token_type: string;
|
|
};
|
|
|
|
type SpreadsheetMetadata = {
|
|
sheets?: Array<{
|
|
properties?: {
|
|
sheetId?: number;
|
|
title?: string;
|
|
};
|
|
}>;
|
|
};
|
|
|
|
type SheetValuesResponse = {
|
|
range?: string;
|
|
majorDimension?: string;
|
|
values?: string[][];
|
|
};
|
|
|
|
export type RowObject = Record<string, string>;
|
|
|
|
type ValueRangePayload = {
|
|
range: string;
|
|
majorDimension: "ROWS";
|
|
values: string[][];
|
|
};
|
|
|
|
type BatchUpdateSpreadsheetRequest = {
|
|
requests: Array<{
|
|
addSheet?: {
|
|
properties?: {
|
|
title?: string;
|
|
};
|
|
};
|
|
}>;
|
|
};
|
|
|
|
const TARGET_HEADER_ALIASES: Record<(typeof TARGET_HEADERS)[number], string[]> =
|
|
{
|
|
id: ["id"],
|
|
created_time: ["created_time"],
|
|
campaign_name: ["campaign_name", "campaign_name"],
|
|
form_name: ["form_name"],
|
|
platform: ["platform"],
|
|
full_name: ["full_name"],
|
|
phone_number: ["phone_number"],
|
|
site: ["site"],
|
|
created_at: ["접수일자"],
|
|
activation_channel: ["개통처"],
|
|
customer_group: ["고객그룹"],
|
|
sales_point: ["판매점"],
|
|
contact_number: ["phone_number"],
|
|
primary_product: ["1차 상품"],
|
|
status: ["진행상태"],
|
|
key: ["고객키"],
|
|
};
|
|
|
|
function readRequiredEnv(name: string): string {
|
|
const value = Bun.env[name]?.trim();
|
|
if (!value) {
|
|
throw new Error(`${name} 환경변수가 필요합니다.`);
|
|
}
|
|
return value;
|
|
}
|
|
|
|
function parseSpreadsheetUrl(input: string) {
|
|
const url = new URL(input);
|
|
const match = url.pathname.match(/\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/);
|
|
if (!match?.[1]) {
|
|
throw new Error("유효한 구글 시트 URL이 아닙니다.");
|
|
}
|
|
|
|
const spreadsheetId = match[1];
|
|
const gidValue =
|
|
url.searchParams.get("gid") ?? url.hash.match(/gid=(\d+)/)?.[1] ?? null;
|
|
const gid = gidValue ? Number(gidValue) : null;
|
|
|
|
if (gidValue && Number.isNaN(gid)) {
|
|
throw new Error("gid 값을 해석하지 못했습니다.");
|
|
}
|
|
|
|
return { spreadsheetId, gid };
|
|
}
|
|
|
|
function escapeSheetTitle(title: string): string {
|
|
return `'${title.replaceAll("'", "''")}'`;
|
|
}
|
|
|
|
type SourceSheetEntry = {
|
|
sheetName: string;
|
|
sheetUrl: string;
|
|
};
|
|
|
|
async function readSourceSheetEntries(): Promise<SourceSheetEntry[]> {
|
|
const content = await Bun.file(SHEET_URLS_PATH).text();
|
|
|
|
return content
|
|
.split(/\r?\n/)
|
|
.map((line) => line.trim())
|
|
.filter((line) => line.length > 0 && !line.startsWith("#"))
|
|
.map((line) => {
|
|
const [sheetUrl, ...sheetNameParts] = line.split(/\s+/);
|
|
const sheetName = sheetNameParts.join(" ").trim();
|
|
|
|
if (!sheetUrl || !sheetName) {
|
|
throw new Error(
|
|
`sheetUrls.txt 형식이 잘못되었습니다: "${line}". "sheetUrl sheetName" 형식이어야 합니다.`
|
|
);
|
|
}
|
|
|
|
return {
|
|
sheetUrl,
|
|
sheetName,
|
|
};
|
|
});
|
|
}
|
|
|
|
async function readSavedToken(): Promise<SavedToken | null> {
|
|
const tokenFile = Bun.file(TOKEN_PATH);
|
|
if (!(await tokenFile.exists())) {
|
|
return null;
|
|
}
|
|
|
|
return (await tokenFile.json()) as SavedToken;
|
|
}
|
|
|
|
async function saveToken(token: SavedToken) {
|
|
await mkdir(".tokens", { recursive: true });
|
|
await Bun.write(TOKEN_PATH, JSON.stringify(token, null, 2));
|
|
}
|
|
|
|
async function refreshAccessToken(
|
|
clientId: string,
|
|
clientSecret: string,
|
|
refreshToken: string
|
|
): Promise<SavedToken> {
|
|
const response = await fetch("https://oauth2.googleapis.com/token", {
|
|
method: "POST",
|
|
headers: {
|
|
"Content-Type": "application/x-www-form-urlencoded",
|
|
},
|
|
body: new URLSearchParams({
|
|
client_id: clientId,
|
|
client_secret: clientSecret,
|
|
grant_type: "refresh_token",
|
|
refresh_token: refreshToken,
|
|
}),
|
|
});
|
|
|
|
if (!response.ok) {
|
|
const text = await response.text();
|
|
throw new Error(`토큰 갱신 실패: ${response.status} ${text}`);
|
|
}
|
|
|
|
const token = (await response.json()) as TokenResponse;
|
|
return {
|
|
access_token: token.access_token,
|
|
expiry_date: Date.now() + token.expires_in * 1000,
|
|
refresh_token: refreshToken,
|
|
scope: token.scope,
|
|
token_type: token.token_type,
|
|
};
|
|
}
|
|
|
|
async function requestNewToken(
|
|
clientId: string,
|
|
clientSecret: string
|
|
): Promise<SavedToken> {
|
|
const redirectPort = Number(
|
|
Bun.env.GOOGLE_REDIRECT_PORT ?? DEFAULT_REDIRECT_PORT
|
|
);
|
|
const redirectUri = `http://127.0.0.1:${redirectPort}${CALLBACK_PATH}`;
|
|
const state = crypto.randomUUID();
|
|
|
|
const authUrl = new URL("https://accounts.google.com/o/oauth2/v2/auth");
|
|
authUrl.searchParams.set("client_id", clientId);
|
|
authUrl.searchParams.set("redirect_uri", redirectUri);
|
|
authUrl.searchParams.set("response_type", "code");
|
|
authUrl.searchParams.set("scope", GOOGLE_AUTH_SCOPE);
|
|
authUrl.searchParams.set("access_type", "offline");
|
|
authUrl.searchParams.set("prompt", "consent");
|
|
authUrl.searchParams.set("state", state);
|
|
|
|
let resolveCode!: (value: string) => void;
|
|
let rejectCode!: (reason?: unknown) => void;
|
|
|
|
const codePromise = new Promise<string>((resolve, reject) => {
|
|
resolveCode = resolve;
|
|
rejectCode = reject;
|
|
});
|
|
|
|
const server = Bun.serve({
|
|
port: redirectPort,
|
|
fetch(request) {
|
|
const url = new URL(request.url);
|
|
if (url.pathname !== CALLBACK_PATH) {
|
|
return new Response("Not found", { status: 404 });
|
|
}
|
|
|
|
const returnedState = url.searchParams.get("state");
|
|
const code = url.searchParams.get("code");
|
|
const error = url.searchParams.get("error");
|
|
|
|
if (error) {
|
|
rejectCode(new Error(`OAuth 인증 실패: ${error}`));
|
|
return new Response(
|
|
"OAuth 인증이 취소되었습니다. 터미널을 확인해 주세요.",
|
|
{ status: 400 }
|
|
);
|
|
}
|
|
|
|
if (returnedState !== state || !code) {
|
|
rejectCode(new Error("OAuth 콜백 검증에 실패했습니다."));
|
|
return new Response("잘못된 OAuth 콜백입니다.", { status: 400 });
|
|
}
|
|
|
|
resolveCode(code);
|
|
return new Response("인증이 완료되었습니다. 터미널로 돌아가 주세요.");
|
|
},
|
|
});
|
|
|
|
console.log("브라우저에서 Google OAuth 인증을 진행해 주세요.");
|
|
console.log(authUrl.toString());
|
|
|
|
try {
|
|
Bun.file(authUrl.toString());
|
|
} catch {
|
|
// URL을 이미 출력했기 때문에 open 실패는 무시합니다.
|
|
}
|
|
|
|
let code: string;
|
|
try {
|
|
code = await Promise.race([
|
|
codePromise,
|
|
new Promise<string>((_, reject) => {
|
|
setTimeout(
|
|
() => reject(new Error("OAuth 인증 대기 시간이 초과되었습니다.")),
|
|
5 * 60 * 1000
|
|
);
|
|
}),
|
|
]);
|
|
} finally {
|
|
server.stop(true);
|
|
}
|
|
|
|
const response = await fetch("https://oauth2.googleapis.com/token", {
|
|
method: "POST",
|
|
headers: {
|
|
"Content-Type": "application/x-www-form-urlencoded",
|
|
},
|
|
body: new URLSearchParams({
|
|
client_id: clientId,
|
|
client_secret: clientSecret,
|
|
code,
|
|
grant_type: "authorization_code",
|
|
redirect_uri: redirectUri,
|
|
}),
|
|
});
|
|
|
|
if (!response.ok) {
|
|
const text = await response.text();
|
|
throw new Error(`OAuth 토큰 발급 실패: ${response.status} ${text}`);
|
|
}
|
|
|
|
const token = (await response.json()) as TokenResponse;
|
|
if (!token.refresh_token) {
|
|
throw new Error(
|
|
"refresh_token 을 받지 못했습니다. OAuth 클라이언트 설정을 확인해 주세요."
|
|
);
|
|
}
|
|
|
|
return {
|
|
access_token: token.access_token,
|
|
expiry_date: Date.now() + token.expires_in * 1000,
|
|
refresh_token: token.refresh_token,
|
|
scope: token.scope,
|
|
token_type: token.token_type,
|
|
};
|
|
}
|
|
|
|
async function getAuthorizedToken() {
|
|
const clientId = readRequiredEnv("GOOGLE_CLIENT_ID");
|
|
const clientSecret = readRequiredEnv("GOOGLE_CLIENT_SECRET");
|
|
|
|
const savedToken = await readSavedToken();
|
|
const hasWriteScope = savedToken?.scope?.includes(GOOGLE_AUTH_SCOPE) ?? false;
|
|
|
|
if (savedToken?.refresh_token && hasWriteScope) {
|
|
const needsRefresh = savedToken.expiry_date <= Date.now() + 60_000;
|
|
if (!needsRefresh) {
|
|
return savedToken;
|
|
}
|
|
|
|
const refreshed = await refreshAccessToken(
|
|
clientId,
|
|
clientSecret,
|
|
savedToken.refresh_token
|
|
);
|
|
await saveToken(refreshed);
|
|
return refreshed;
|
|
}
|
|
|
|
if (savedToken?.refresh_token && !hasWriteScope) {
|
|
console.log(
|
|
"기존 토큰이 읽기 전용 scope 입니다. 브라우저에서 다시 OAuth 인증을 진행합니다."
|
|
);
|
|
}
|
|
|
|
const newToken = await requestNewToken(clientId, clientSecret);
|
|
await saveToken(newToken);
|
|
return newToken;
|
|
}
|
|
|
|
async function googleApiFetch<T>(accessToken: string, url: string): Promise<T> {
|
|
const response = await fetch(url, {
|
|
headers: {
|
|
Authorization: `Bearer ${accessToken}`,
|
|
},
|
|
});
|
|
|
|
if (!response.ok) {
|
|
const text = await response.text();
|
|
throw new Error(`Google API 호출 실패: ${response.status} ${text}`);
|
|
}
|
|
|
|
return (await response.json()) as T;
|
|
}
|
|
|
|
async function googleApiRequest<T>(
|
|
accessToken: string,
|
|
url: string,
|
|
init?: RequestInit
|
|
): Promise<T> {
|
|
const response = await fetch(url, {
|
|
...init,
|
|
headers: {
|
|
Authorization: `Bearer ${accessToken}`,
|
|
...(init?.headers ?? {}),
|
|
},
|
|
});
|
|
|
|
if (!response.ok) {
|
|
const text = await response.text();
|
|
throw new Error(`Google API 호출 실패: ${response.status} ${text}`);
|
|
}
|
|
|
|
return (await response.json()) as T;
|
|
}
|
|
|
|
async function getSheetTitleByGid(
|
|
accessToken: string,
|
|
spreadsheetId: string,
|
|
gid: number | null
|
|
): Promise<string> {
|
|
const metadata = await googleApiFetch<SpreadsheetMetadata>(
|
|
accessToken,
|
|
`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}?fields=sheets.properties(sheetId,title)`
|
|
);
|
|
|
|
const sheets = metadata.sheets ?? [];
|
|
if (sheets.length === 0) {
|
|
throw new Error("스프레드시트에 시트 탭이 없습니다.");
|
|
}
|
|
|
|
if (gid == null) {
|
|
const firstTitle = sheets[0]?.properties?.title;
|
|
if (!firstTitle) {
|
|
throw new Error("첫 번째 시트의 이름을 찾지 못했습니다.");
|
|
}
|
|
return firstTitle;
|
|
}
|
|
|
|
const matched = sheets.find((sheet) => sheet.properties?.sheetId === gid)
|
|
?.properties?.title;
|
|
if (!matched) {
|
|
throw new Error(`gid=${gid} 에 해당하는 시트 탭을 찾지 못했습니다.`);
|
|
}
|
|
return matched;
|
|
}
|
|
|
|
async function hasSheetTitle(
|
|
accessToken: string,
|
|
spreadsheetId: string,
|
|
sheetTitle: string
|
|
): Promise<boolean> {
|
|
const metadata = await googleApiFetch<SpreadsheetMetadata>(
|
|
accessToken,
|
|
`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}?fields=sheets.properties(title)`
|
|
);
|
|
|
|
return (
|
|
metadata.sheets?.some((sheet) => sheet.properties?.title === sheetTitle) ??
|
|
false
|
|
);
|
|
}
|
|
|
|
async function createSheet(
|
|
accessToken: string,
|
|
spreadsheetId: string,
|
|
sheetTitle: string
|
|
) {
|
|
const body: BatchUpdateSpreadsheetRequest = {
|
|
requests: [
|
|
{
|
|
addSheet: {
|
|
properties: {
|
|
title: sheetTitle,
|
|
},
|
|
},
|
|
},
|
|
],
|
|
};
|
|
|
|
return googleApiRequest(
|
|
accessToken,
|
|
`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}:batchUpdate`,
|
|
{
|
|
method: "POST",
|
|
headers: {
|
|
"Content-Type": "application/json",
|
|
},
|
|
body: JSON.stringify(body),
|
|
}
|
|
);
|
|
}
|
|
|
|
async function ensureSheetExists(
|
|
accessToken: string,
|
|
spreadsheetId: string,
|
|
sheetTitle: string
|
|
) {
|
|
const exists = await hasSheetTitle(accessToken, spreadsheetId, sheetTitle);
|
|
if (exists) {
|
|
return;
|
|
}
|
|
|
|
await createSheet(accessToken, spreadsheetId, sheetTitle);
|
|
}
|
|
|
|
function buildRange(sheetTitle: string, userRange?: string): string {
|
|
if (!userRange) {
|
|
return escapeSheetTitle(sheetTitle);
|
|
}
|
|
|
|
if (userRange.includes("!")) {
|
|
return userRange;
|
|
}
|
|
|
|
return `${escapeSheetTitle(sheetTitle)}!${userRange}`;
|
|
}
|
|
|
|
function formatKstDateTime(value: string): string {
|
|
const trimmed = value.trim();
|
|
if (!trimmed) {
|
|
return "";
|
|
}
|
|
|
|
const date = new Date(trimmed);
|
|
if (Number.isNaN(date.getTime())) {
|
|
return value;
|
|
}
|
|
|
|
const parts = new Intl.DateTimeFormat("en-CA", {
|
|
timeZone: "Asia/Seoul",
|
|
year: "numeric",
|
|
month: "2-digit",
|
|
day: "2-digit",
|
|
hour: "2-digit",
|
|
minute: "2-digit",
|
|
second: "2-digit",
|
|
hour12: false,
|
|
})
|
|
.formatToParts(date)
|
|
.reduce<Record<string, string>>((acc, part) => {
|
|
if (part.type !== "literal") {
|
|
acc[part.type] = part.value;
|
|
}
|
|
return acc;
|
|
}, {});
|
|
|
|
return `${parts.year}-${parts.month}-${parts.day} ${parts.hour}:${parts.minute}:${parts.second}`;
|
|
}
|
|
|
|
function formatPhoneNumber(value: string): string {
|
|
const trimmed = value.trim();
|
|
if (!trimmed) {
|
|
return "";
|
|
}
|
|
|
|
const normalized = trimmed.replace(/^p:/i, "").replace(/[^\d+]/g, "");
|
|
let digits = normalized;
|
|
|
|
if (digits.startsWith("+82")) {
|
|
digits = `0${digits.slice(3)}`;
|
|
} else if (digits.startsWith("82")) {
|
|
digits = `0${digits.slice(2)}`;
|
|
}
|
|
|
|
const phoneDigits = digits.replace(/\D/g, "");
|
|
if (phoneDigits.length === 10 && phoneDigits.startsWith("10")) {
|
|
return `010-${phoneDigits.slice(2, 6)}-${phoneDigits.slice(6)}`;
|
|
}
|
|
|
|
if (phoneDigits.length === 11) {
|
|
return `${phoneDigits.slice(0, 3)}-${phoneDigits.slice(3, 7)}-${phoneDigits.slice(7)}`;
|
|
}
|
|
|
|
return value;
|
|
}
|
|
|
|
function normalizeCellValue(header: string, value: string): string {
|
|
const normalizedHeader = normalizeHeader(header);
|
|
|
|
if (normalizedHeader === "created_time") {
|
|
return formatKstDateTime(value);
|
|
}
|
|
|
|
if (normalizedHeader === "phone_number") {
|
|
return formatPhoneNumber(value);
|
|
}
|
|
|
|
return value;
|
|
}
|
|
|
|
function formatKstDateString(date: Date): string {
|
|
const parts = new Intl.DateTimeFormat("en-CA", {
|
|
timeZone: "Asia/Seoul",
|
|
year: "numeric",
|
|
month: "2-digit",
|
|
day: "2-digit",
|
|
})
|
|
.formatToParts(date)
|
|
.reduce<Record<string, string>>((acc, part) => {
|
|
if (part.type !== "literal") {
|
|
acc[part.type] = part.value;
|
|
}
|
|
return acc;
|
|
}, {});
|
|
|
|
return `${parts.year}-${parts.month}-${parts.day}`;
|
|
}
|
|
|
|
function getTodayKstDateString(): string {
|
|
return formatKstDateString(new Date());
|
|
}
|
|
|
|
function isValidKstDateString(value: string): boolean {
|
|
if (!/^\d{4}-\d{2}-\d{2}$/.test(value)) {
|
|
return false;
|
|
}
|
|
|
|
return formatKstDateString(new Date(`${value}T00:00:00+09:00`)) === value;
|
|
}
|
|
|
|
function filterRowsByCreatedDate(rows: RowObject[], filterDate: string): RowObject[] {
|
|
return rows.filter((row) => row.created_time?.startsWith(filterDate));
|
|
}
|
|
|
|
function getKstMonthDayString(dateString: string): string {
|
|
return dateString.slice(5).replace("-", "");
|
|
}
|
|
|
|
type RunOptions = {
|
|
filterDate: string;
|
|
sourceRange?: string;
|
|
targetSheetUrl: string;
|
|
};
|
|
|
|
function parseRunOptions(argv: string[]): RunOptions {
|
|
const args = argv.slice(2);
|
|
const positionalArgs: string[] = [];
|
|
let filterDate = Bun.env.GOOGLE_FILTER_DATE?.trim() || undefined;
|
|
let sourceRange = Bun.env.GOOGLE_SHEET_RANGE?.trim() || undefined;
|
|
let targetSheetUrl =
|
|
Bun.env.GOOGLE_TARGET_SHEET_URL?.trim() || DEFAULT_TARGET_SHEET_URL;
|
|
|
|
for (let index = 0; index < args.length; index += 1) {
|
|
const arg = args[index];
|
|
|
|
if (arg === "--date") {
|
|
const nextArg = args[index + 1]?.trim();
|
|
if (!nextArg) {
|
|
throw new Error("--date 옵션에는 YYYY-MM-DD 값을 함께 넣어야 합니다.");
|
|
}
|
|
filterDate = nextArg;
|
|
index += 1;
|
|
continue;
|
|
}
|
|
|
|
if (arg.startsWith("--date=")) {
|
|
filterDate = arg.slice("--date=".length).trim();
|
|
if (!filterDate) {
|
|
throw new Error("--date 옵션에는 YYYY-MM-DD 값을 함께 넣어야 합니다.");
|
|
}
|
|
continue;
|
|
}
|
|
|
|
positionalArgs.push(arg);
|
|
}
|
|
|
|
if (positionalArgs[0]) {
|
|
sourceRange = positionalArgs[0];
|
|
}
|
|
|
|
if (positionalArgs[1]) {
|
|
targetSheetUrl = positionalArgs[1];
|
|
}
|
|
|
|
const resolvedFilterDate = filterDate || getTodayKstDateString();
|
|
if (!isValidKstDateString(resolvedFilterDate)) {
|
|
throw new Error(
|
|
`추출 날짜 형식이 잘못되었습니다: "${resolvedFilterDate}". YYYY-MM-DD 형식으로 입력해 주세요.`
|
|
);
|
|
}
|
|
|
|
return {
|
|
filterDate: resolvedFilterDate,
|
|
sourceRange,
|
|
targetSheetUrl,
|
|
};
|
|
}
|
|
|
|
function rowsToObjects(values: string[][]): RowObject[] {
|
|
const [headerRow, ...dataRows] = values;
|
|
if (!headerRow || headerRow.length === 0) {
|
|
return [];
|
|
}
|
|
|
|
return dataRows.map((row) => {
|
|
const entry: RowObject = {};
|
|
|
|
headerRow.forEach((rawHeader, index) => {
|
|
const fallbackHeader = `column_${index + 1}`;
|
|
const header = rawHeader?.trim() || fallbackHeader;
|
|
entry[header] = normalizeCellValue(header, row[index] ?? "");
|
|
});
|
|
|
|
return entry;
|
|
});
|
|
}
|
|
|
|
function normalizeHeader(header: string): string {
|
|
return header.trim().toLowerCase().replaceAll(/\s+/g, "_");
|
|
}
|
|
|
|
function buildHeaderLookup(headers: string[]): Map<string, string> {
|
|
const lookup = new Map<string, string>();
|
|
|
|
headers.forEach((header, index) => {
|
|
const trimmed = header.trim() || `column_${index + 1}`;
|
|
lookup.set(trimmed, trimmed);
|
|
lookup.set(normalizeHeader(trimmed), trimmed);
|
|
});
|
|
|
|
return lookup;
|
|
}
|
|
|
|
function mapRowsForTarget(
|
|
rows: RowObject[],
|
|
sourceHeaders: string[]
|
|
): string[][] {
|
|
const lookup = buildHeaderLookup(sourceHeaders);
|
|
|
|
return rows.map((row) =>
|
|
TARGET_HEADERS.map((targetHeader) => {
|
|
const directValue = row[targetHeader];
|
|
if (directValue != null) {
|
|
return directValue;
|
|
}
|
|
|
|
const mappedSourceHeader =
|
|
lookup.get(targetHeader) ?? lookup.get(normalizeHeader(targetHeader));
|
|
|
|
if (mappedSourceHeader) {
|
|
return row[mappedSourceHeader] ?? "";
|
|
}
|
|
|
|
const aliases = TARGET_HEADER_ALIASES[targetHeader] ?? [];
|
|
for (const alias of aliases) {
|
|
if (row[alias] != null) {
|
|
return row[alias] ?? "";
|
|
}
|
|
|
|
const aliasHeader =
|
|
lookup.get(alias) ?? lookup.get(normalizeHeader(alias));
|
|
if (aliasHeader) {
|
|
return row[aliasHeader] ?? "";
|
|
}
|
|
}
|
|
|
|
return "";
|
|
})
|
|
);
|
|
}
|
|
|
|
async function getSheetValues(
|
|
accessToken: string,
|
|
spreadsheetId: string,
|
|
range: string
|
|
): Promise<SheetValuesResponse> {
|
|
const encodedRange = encodeURIComponent(range);
|
|
return googleApiFetch<SheetValuesResponse>(
|
|
accessToken,
|
|
`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${encodedRange}`
|
|
);
|
|
}
|
|
|
|
async function updateSheetValues(
|
|
accessToken: string,
|
|
spreadsheetId: string,
|
|
range: string,
|
|
values: string[][]
|
|
) {
|
|
const encodedRange = encodeURIComponent(range);
|
|
const body: ValueRangePayload = {
|
|
range,
|
|
majorDimension: "ROWS",
|
|
values,
|
|
};
|
|
|
|
return googleApiRequest(
|
|
accessToken,
|
|
`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${encodedRange}?valueInputOption=USER_ENTERED`,
|
|
{
|
|
method: "PUT",
|
|
headers: {
|
|
"Content-Type": "application/json",
|
|
},
|
|
body: JSON.stringify(body),
|
|
}
|
|
);
|
|
}
|
|
|
|
async function appendSheetValues(
|
|
accessToken: string,
|
|
spreadsheetId: string,
|
|
range: string,
|
|
values: string[][]
|
|
) {
|
|
const encodedRange = encodeURIComponent(range);
|
|
const body: ValueRangePayload = {
|
|
range,
|
|
majorDimension: "ROWS",
|
|
values,
|
|
};
|
|
|
|
return googleApiRequest(
|
|
accessToken,
|
|
`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${encodedRange}:append?valueInputOption=USER_ENTERED&insertDataOption=INSERT_ROWS`,
|
|
{
|
|
method: "POST",
|
|
headers: {
|
|
"Content-Type": "application/json",
|
|
},
|
|
body: JSON.stringify(body),
|
|
}
|
|
);
|
|
}
|
|
|
|
async function ensureTargetHeaderRow(
|
|
accessToken: string,
|
|
spreadsheetId: string,
|
|
sheetTitle: string
|
|
) {
|
|
const headerRange = `${escapeSheetTitle(sheetTitle)}!1:1`;
|
|
const current = await getSheetValues(accessToken, spreadsheetId, headerRange);
|
|
const existingHeaders = current.values?.[0] ?? [];
|
|
const hasSameHeaders =
|
|
existingHeaders.length === TARGET_HEADERS.length &&
|
|
TARGET_HEADERS.every((header, index) => existingHeaders[index] === header);
|
|
|
|
if (hasSameHeaders) {
|
|
return;
|
|
}
|
|
|
|
await updateSheetValues(accessToken, spreadsheetId, headerRange, [
|
|
Array.from(TARGET_HEADERS),
|
|
]);
|
|
}
|
|
|
|
async function processSourceSheet(
|
|
token: SavedToken,
|
|
targetSheet: ReturnType<typeof parseSpreadsheetUrl>,
|
|
filterDate: string,
|
|
sourceRange: string | undefined,
|
|
cafe24: HyundaiCustomerVerifier,
|
|
entry: SourceSheetEntry
|
|
) {
|
|
const sourceSheet = parseSpreadsheetUrl(entry.sheetUrl);
|
|
const sourceSheetTitle = await getSheetTitleByGid(
|
|
token.access_token,
|
|
sourceSheet.spreadsheetId,
|
|
sourceSheet.gid
|
|
);
|
|
|
|
const range = buildRange(sourceSheetTitle, sourceRange);
|
|
const sourceValues = await getSheetValues(
|
|
token.access_token,
|
|
sourceSheet.spreadsheetId,
|
|
range
|
|
);
|
|
const sourceRows = sourceValues.values ?? [];
|
|
|
|
console.log(`sourceSpreadsheetId: ${sourceSheet.spreadsheetId}`);
|
|
console.log(`sourceSheetTitle: ${sourceSheetTitle}`);
|
|
console.log(`sourceRange: ${sourceValues.range ?? range}`);
|
|
console.log("");
|
|
|
|
if (sourceRows.length === 0) {
|
|
console.log("가져온 데이터가 없습니다.");
|
|
return;
|
|
}
|
|
|
|
const [headerRow] = sourceRows;
|
|
let rows = rowsToObjects(sourceRows);
|
|
|
|
rows = filterRowsByCreatedDate(rows, filterDate);
|
|
// rows = take(rows, 5);
|
|
console.log(`filterDate: ${filterDate}`);
|
|
console.log("행갯수:", rows.length);
|
|
rows = await cafe24.lookupPhones(rows);
|
|
|
|
const mappedRows = mapRowsForTarget(rows, headerRow ?? []);
|
|
const datedTargetSheetTitle = `${entry.sheetName}${getKstMonthDayString(filterDate)}`;
|
|
|
|
// console.log("sourceHeaders:");
|
|
// console.log(JSON.stringify(headerRow ?? [], null, 2));
|
|
// console.log("");
|
|
// console.log("mappedPreview:");
|
|
// console.log(JSON.stringify(mappedRows.slice(0, 3), null, 2));
|
|
// console.log("");
|
|
|
|
if (mappedRows.length === 0) {
|
|
console.log("대상 시트에 추가할 데이터 행이 없습니다.");
|
|
return;
|
|
}
|
|
|
|
await ensureSheetExists(
|
|
token.access_token,
|
|
targetSheet.spreadsheetId,
|
|
datedTargetSheetTitle
|
|
);
|
|
|
|
await ensureTargetHeaderRow(
|
|
token.access_token,
|
|
targetSheet.spreadsheetId,
|
|
datedTargetSheetTitle
|
|
);
|
|
|
|
await appendSheetValues(
|
|
token.access_token,
|
|
targetSheet.spreadsheetId,
|
|
escapeSheetTitle(datedTargetSheetTitle),
|
|
mappedRows
|
|
);
|
|
|
|
console.log(`targetSpreadsheetId: ${targetSheet.spreadsheetId}`);
|
|
console.log(`targetSheetTitle: ${datedTargetSheetTitle}`);
|
|
console.log(`appendedRows: ${mappedRows.length}`);
|
|
}
|
|
|
|
async function main() {
|
|
const { filterDate, sourceRange, targetSheetUrl } = parseRunOptions(Bun.argv);
|
|
|
|
const entries = await readSourceSheetEntries();
|
|
if (entries.length === 0) {
|
|
throw new Error("sheetUrls.txt 에 처리할 시트가 없습니다.");
|
|
}
|
|
|
|
const targetSheet = parseSpreadsheetUrl(targetSheetUrl);
|
|
const token = await getAuthorizedToken();
|
|
const cafe24 = new HyundaiCustomerVerifier({});
|
|
await cafe24.init();
|
|
|
|
for (const entry of entries) {
|
|
await processSourceSheet(
|
|
token,
|
|
targetSheet,
|
|
filterDate,
|
|
sourceRange,
|
|
cafe24,
|
|
entry
|
|
);
|
|
}
|
|
}
|
|
|
|
main().catch((error) => {
|
|
console.error(error instanceof Error ? error.message : error);
|
|
process.exit(1);
|
|
});
|