【GAS、Google Spreadsheet】ドライブにある画像をセルに表示しまくるツール

はじまり

ツールの概要

このツールを実行すると、Googleドライブにある画像のファイル名と同じ名前のサイトと対応する箇所にIMAGE関数を入力しまくってくれます。

ソース

function listFormated(listReadFromGss) {
  var listFormated = [];
  for (let j = 0; j < listReadFromGss.length; j++) {
    // "if" statement in one liner. If '', nothing to do.
    listReadFromGss[j][0]=='' ? true : listFormated.push(listReadFromGss[j][0]);
  }
  return listFormated
}

/**
 * Get number of record in Google Spreadsheet.
 *
 * @param {"bookmarkSites"} sheetName - Name of sheet that you wanna know number of record.
 * @return Number of record
 * @customfunction
 */
function get_row_to_read_actual_in_GSS(sheetName) {
  // declare list for warning message.
  var warningMessage = 'Warning: Number of row passing over \"row_to_read\". Tweak me.';
  var errorMessage   = 'RowIndexOutOfBoundsError: Number of row reached \"row_to_read\". Tweak me.';

  // declare variables for row and column index.
  var row_to_read = 201;
  var row_to_read_actual;

  // declare list.
  var idList;

  // get sheet.
  var ss    = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName(sheetName);

  // memorize number of row to read
  console.time(`SELECT TOP ${row_to_read - 1} id FROM \'${sheetName}\'`);
  idList = sheet.getRange(2, column_for_siteBookmark_of_id, row_to_read - 1, 1).getValues();
  console.timeEnd(`SELECT TOP ${row_to_read - 1} id FROM \'${sheetName}\'`);
  idList_formated = listFormated(idList);
  
  // warning message. If condition is false, nothing to do.
  row_to_read_actual = Number(idList_formated.reduce((a,b)=>Math.max(a,b)));
  row_to_read - row_to_read_actual <= 2 ? console.warn(warningMessage) : false;
  if(row_to_read_actual >= row_to_read - 1){
    console.error(errorMessage);
    return 0;
  }
  return row_to_read_actual;
}

/**
 * Get list to set image onto Google Spreadsheet.
 *
 * @return [[number], [string]] - recordId and fileFullname
 * @customfunction
 */
function getListToSetImage() {
  // declare variables for prepare.
  var ss,
    sheet;
  var bookmarkList;
  ss    = SpreadsheetApp.getActive();
  sheet = ss.getSheetByName(sheetName2nd);

  var row_to_read_actual = get_row_to_read_actual_in_GSS(sheetName2nd);
  if(row_to_read_actual == 0){
    console.error(errorMessageList[0]);
    return false;
  }

  // Get files' name and fullname to JSON format.
  var folder = DriveApp.getFolderById(iconFolderId),
      files  = folder.getFiles(),
      file;
  var fileNameList            = [],
      fileFullNameList        = [],
      fileNameDict            = {},
      iconInsertingRecordList = [];
  while(files.hasNext()) {
    file     = files.next();
    fileNameList.push(file.getName().match(/([^/]*)\./)[1]);
    fileFullNameList.push(file.getName())
  }
  fileNameDict['name']     = fileNameList;
  fileNameDict['fullName'] = fileFullNameList;
  
  // Get siteName list from GSS and extract record that fileName and siteName are same.
  bookmarkList = sheet.getRange(row_for_siteBookmark_of_firstRecord, column_for_siteBookmark_of_id, row_to_read_actual, column_for_siteBookmark_of_icon).getValues();
  bookmarkList.forEach(function(record) {
    if (record[column_for_siteBookmark_of_icon - 1] == '') {
      for (let i = 0; i < fileNameDict['name'].length; i++){
        if (fileNameDict['name'][i] == record[column_for_siteBookmark_of_siteName - 1]) {
          iconInsertingRecordList.push([record[column_for_siteBookmark_of_id - 1], fileNameDict['fullName'][i]]);
          break;
        }
      }
    }else{
      // nothing to do.
    }
  });
  
  return iconInsertingRecordList;
}

function serializeArray(targetArray){
  var onlyStringArray = [];
  targetArray.forEach(item => {
    onlyStringArray.push(`[${String(item)}]`);
  });
  var serializedArray = onlyStringArray.join(',');
  return serializedArray;
}

function setImageFromList(listToSetImage) {
  // declare variables.
  var file;
  var iconUrl,
    formattedUrl;
  var ss    = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName(sheetName2nd);

  console.info(`INFO: listToSetImage is ${serializeArray(listToSetImage)}`);
  
  // In this case, not in-cell image but OverGridImage...
  // var file = DriveApp.getFolderById(iconFolderId).getFilesByName('note.png').next();
  // var fileBlob = file.getBlob();
  // var insertedImage = sheet.insertImage(fileBlob, column_for_siteBookmark_of_icon, 4);
  // insertedImage.setAnchorCell(sheet.getRange(4, column_for_siteBookmark_of_icon));

  // execute this code to set file permission and set in-cell image.
  listToSetImage.forEach(record => {
    file = DriveApp.getFolderById(iconFolderId).getFilesByName(record[1]).next();
    file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
    iconUrl = file.getUrl();
    formattedUrl = iconUrl.replace('file/d/', 'uc?export=download&id=').replace('/view?usp=drivesdk', '')
    sheet.getRange(record[0] + row_for_siteBookmark_of_firstRecord - 2, column_for_siteBookmark_of_icon).setFormula(`=IMAGE(\"${formattedUrl}\")`)
  });
}

function setImageFromGoogleDrive() {
  var listToSetImage = getListToSetImage();
  console.time(`UPDATE \'${sheetName2nd}\' SET favicon = ∀image WHERE favicon = ''`);
  setImageFromList(listToSetImage);
  console.timeEnd(`UPDATE \'${sheetName2nd}\' SET favicon = ∀image WHERE favicon = ''`);
}

ソースの解説

以下、ソースの解説になります。

関数起動

まず、setImageFromGoogleDrive()をキックします。

function setImageFromGoogleDrive() {
  var listToSetImage = getListToSetImage();
  console.time(`UPDATE \'${sheetName2nd}\' SET favicon = ∀image WHERE favicon = ''`);
  setImageFromList(listToSetImage);
  console.timeEnd(`UPDATE \'${sheetName2nd}\' SET favicon = ∀image WHERE favicon = ''`);
}

getListToSetImage()

まず、変数を宣言して、この部分ではget_row_to_read_actual_in_GSS()を利用して、実際にシート内にレコードが何件あるのかを数えて、処理対象の行数を取得します。

  var row_to_read_actual = get_row_to_read_actual_in_GSS(sheetName2nd);
  if(row_to_read_actual == 0){
    console.error(errorMessageList[0]);
    return false;
  }

ファイル名の拡張子を除いた分を取得する

そして、次にアイコンにする画像ファイルが入っているフォルダから、画像ファイルを全て取得してファイル名も取得します。以下で、拡張子が除かれたファイル名が取得できます。

    fileNameList.push(file.getName().match(/([^/]*)\./)[1]);

アイコンを追加する行を取得する

アイコン用の画像ファイルがあり、かつアイコンがまだ追加されていない行を総当たりさせて、両方とも満たす行をiconInsertingRecordListの配列にpushします。

  bookmarkList.forEach(function(record) {
    if (record[column_for_siteBookmark_of_icon - 1] == '') {
      for (let i = 0; i < fileNameDict['name'].length; i++){
        if (fileNameDict['name'][i] == record[column_for_siteBookmark_of_siteName - 1]) {
          iconInsertingRecordList.push([record[column_for_siteBookmark_of_id - 1], fileNameDict['fullName'][i]]);
          break;
        }
      }
    }else{
      // nothing to do.
    }
  });

ログも取る

次に、先程取得した行の情報を元にアイコンを表示するため、スプシにIMAGE関数を入れる処理になります。

console.time()とconsole.timeEnd()で挟まれている処理にどれだけ時間が掛かったかを計測します。この処理では、おおよそIMAGE関数を入れる処理で時間を食いがちです。（追加するアイコンが３つある場合は５秒位掛かります。）

function setImageFromGoogleDrive() {
  var listToSetImage = getListToSetImage();
  console.time(`UPDATE \'${sheetName2nd}\' SET favicon = ∀image WHERE favicon = ''`);
  setImageFromList(listToSetImage);
  console.timeEnd(`UPDATE \'${sheetName2nd}\' SET favicon = ∀image WHERE favicon = ''`);
}

IMAGE関数を入れる

ここが実際に入れている処理になります。

まず、setSharing()で、画像ファイルを共有ファイルにする必要があります。共有ファイルにしないとIMAGE関数で表示されません。

次に、replace()でクエリパラメータや参照モードを示す/viewの文字列を消して、画像ファイルの純粋なURLを取得する必要があります。

これらの前処理をして、やっとIMAGE関数でセル内に画像を表示させることが出来ます。このsetFormula()に時間が掛かります・・・！

  listToSetImage.forEach(record => {
    file = DriveApp.getFolderById(iconFolderId).getFilesByName(record[1]).next();
    file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
    iconUrl = file.getUrl();
    formattedUrl = iconUrl.replace('file/d/', 'uc?export=download&id=').replace('/view?usp=drivesdk', '')
    sheet.getRange(record[0] + row_for_siteBookmark_of_firstRecord - 2, column_for_siteBookmark_of_icon).setFormula(`=IMAGE(\"${formattedUrl}\")`)
  });

完了

はい！　Googleドライブにある画像をアイコンに出来ました！

おしまい

以上になります！