Google スプレッドシート: シート削除

// OAuth2 config
// Authorization Endpoint URL: https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
// Token Endpoint URL: https://accounts.google.com/o/oauth2/token
// Scope: https://www.googleapis.com/auth/spreadsheets
// Consumer Key: (Get by Google Developers Console)
// Consumer Secret: (Get by Google Developers Console)

main();
function main() {
    //// == 工程コンフィグの参照 / Config Retrieving ==
    const oauth2 = configs.get("conf_OAuth2");
    const spreadSheetId = retrieveSpreadSheetId();
    const sheetTitle = configs.get("conf_SheetTitle");
    if (sheetTitle === "" || sheetTitle === null) {
        throw "Sheet Title is empty.";
    }

    //// == Calculating / 演算 ==
    const sheetId = getSheetId(oauth2, spreadSheetId, sheetTitle);

    deleteSheet(
        oauth2,
        spreadSheetId,
        sheetId,
    );
}

/**
 * config からスプレッドシートの ID を読み出す
 * @return {String} spreadSheetId スプレッドシートの ID
 */
function retrieveSpreadSheetId() {
    let spreadSheetId = configs.get("conf_SpreadSheetId");
    const spreadSheetIdDef = configs.getObject("conf_SpreadSheetId");
    if (spreadSheetIdDef !== null) {
        spreadSheetId = engine.findData(spreadSheetIdDef);
    }
    if (spreadSheetId === null || spreadSheetId === "") {
        throw "Spreadsheet ID is empty.";
    }
    return spreadSheetId;
}

/**
 * Google スプレッドシートのシート ID を取得
 * @param {String} oauth2 OAuth2 認証設定
 * @param {String} spreadSheetId スプレッドシートの ID
 * @param {String} sheetTitle シートタイトル
 * @return {Number} sheetId シート ID
 */
function getSheetId(oauth2, spreadSheetId, sheetTitle) {
    const uri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(
        spreadSheetId
    )}?includeGridData=false`;
    const response = httpClient.begin().authSetting(oauth2).get(uri);
    const status = response.getStatusCode();
    const responseStr = response.getResponseAsString();
    if (status !== 200) {
        engine.log(responseStr);
        throw `Failed to get sheet information. status: ${status}`;
    }
    const { sheets } = JSON.parse(responseStr);
    for (let i = 0; i < sheets.length; i++) {
        if (sheets[i].properties.title === sheetTitle) {
            const sheetId = parseInt(sheets[i].properties.sheetId, 10);
            return sheetId;
        }
    }
    // error (not exist)
    throw `Sheet ${sheetTitle} does not exist`;
}

/**
 * Google スプレッドシートのシートを削除
 * @param {String} oauth2 OAuth2 認証設定
 * @param {String} spreadSheetId スプレッドシートの ID
 * @param {String} sheetId シートの ID
 */
function deleteSheet(oauth2, spreadSheetId, sheetId) {
    // Method: spreadsheets.batchUpdate
    const uri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(
        spreadSheetId
    )}:batchUpdate`;

    const requestObj = {
        requests: [
            {
                deleteSheet: {
                    sheetId: sheetId
                }
            }
        ]
    };

    const response = httpClient
        .begin()
        .authSetting(oauth2)
        .body(JSON.stringify(requestObj), "application/json")
        .post(uri);

    const status = response.getStatusCode();
    const responseStr = response.getResponseAsString();
    if (status >= 300) {
        engine.log(responseStr);
        throw `Failed to delete sheet. status: ${status}`;
    }

    return;
}