Google Sheets: Create File
Creates a new Spreadsheet in a specified folder on Google Drive
2020-10-28 (C) Questetra, Inc. (MIT License)
Configs
  • C1: User who connects to Google Drive *
  • C2: Parent Folder ID (My Drive root if blank)
  • C3: File Name * #{EL}
  • C4: First Sheet Title * #{EL}
  • C5: String type data item to save file ID of Spreadsheet
  • C6: String type data item to save web view url of Spreadsheet
Script
main();
function main() {
  const parentFolderIdDef = configs.getObject("Conf_ParentFolderId");
  let parentFolderId = configs.get("Conf_ParentFolderId");
  if (parentFolderIdDef !== null) {
    parentFolderId = engine.findData(parentFolderIdDef);
  }
  const fileName = configs.get("Conf_FileName");
  if (fileName === "" || fileName === null) {
    throw "File Name is blank";
  }
  const sheetTitle = configs.get("Conf_SheetTitle");
  if (sheetTitle === "" || sheetTitle === null) {
    throw "Sheet Title is blank";
  }
  if (sheetTitle.length > 100) {
    throw "Sheet Title should be 100 characters or less";
  }
  const quser = configs.getObject('Conf_User');
  if (quser === null) {
    throw "User not found";
  }
  engine.log(`User Name: ${quser.getName()}`);
  
  // get OAuth token
  let token;
  try{
    token = httpClient.getGoogleOAuth2Token(quser, "Drive");
  }catch(e){
    throw "This User has not connected with Google Drive."
  }
  
  const {id, webViewLink} = createFile(token, parentFolderId, fileName);
  
  updateSheetTitle(token, id, sheetTitle);
  
  setData("Conf_FileIdItem", id);
  setData("Conf_WebViewUrlItem", webViewLink);
}

/**
  * データ項目に出力する
  * @param {ProcessDataDefinitionView} configName  データ項目の ProcessDataDefinitionView
  * @param {String} data  出力する文字列
  */
function setData(configName, data) {
  const def = configs.getObject(configName);
  if (def === null) {
    return;
  }
  engine.setData(def,  data);
}

/**
  * google スプレッドシートを親フォルダに作成する
  * @param {String} token  OAuth2 のトークン
  * @param {String} parentFolderId  親フォルダ
  * @param {String} fileName  ファイル名
  * @return {Object} createFile  スプレッドシートのファイル IDと表示 URLを格納した JSON オブジェクト
  *   プロパティ: {String} id スプレッドシートのファイル ID
  *                {String} webViewLink スプレッドシートの表示 URL
  */
function createFile(token, parentFolderId, fileName) {
  let jsonReq = {};
  jsonReq["mimeType"] = "application/vnd.google-apps.spreadsheet";
  if (parentFolderId !== "" && parentFolderId !== null){
    jsonReq["parents"] = [ String(parentFolderId) ];
  }
  jsonReq["name"] = String(fileName);
  
  const url = 'https://www.googleapis.com/drive/v3/files/';
  const response = httpClient.begin()
    .bearer(token)
    .queryParam("fields", "id, webViewLink")
    .queryParam("supportsAllDrives", "true")
    .body(JSON.stringify(jsonReq), "application/json; charset=UTF-8")
    .post(url);
  const status = response.getStatusCode();
  const responseTxt = response.getResponseAsString();
  
  engine.log(`status of file create: ${status}`);
  if (status !== 200) {
    const error = `Failed to create \n status: ${status}`;
    engine.log(responseTxt);
    throw error;
  }
  
  const jsonRes = JSON.parse(responseTxt);
  const res = {
    id : jsonRes["id"], 
    webViewLink : jsonRes["webViewLink"]
    }
  return res;
}

/**
  * google スプレッドシートのシート名を更新する
  * @param {String} token  OAuth2 のトークン
  * @param {String} fileId  ファイルID
  * @param {String} sheetTitle  シート名
  */
function updateSheetTitle(token, fileId, sheetTitle) {
  
  let jsonReq = {
    requests: []
  };
  
  jsonReq.requests[0] = {
    updateSheetProperties: {
      fields: 'title',
      properties: {
        sheetId: 0,
        title: sheetTitle
      }
    }
  };
  
  const url = `https://sheets.googleapis.com/v4/spreadsheets/${fileId}:batchUpdate`;
  const response = httpClient.begin()
    .bearer(token)
    .body(JSON.stringify(jsonReq), "application/json")
    .post(url);
  const status = response.getStatusCode();
  const responseTxt = response.getResponseAsString();
  engine.log(`status of sheet title update: ${status}`);
  if (status !== 200) {
    const error = `Failed to update sheet title \n status: ${status}`;
    engine.log(responseTxt);
    throw error;
  }
}

Download

Capture

Notes

  1. The User in C1 must have a configured connection with Google Drive in [Account Settings] > [Google Connectivity]

%d bloggers like this: