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

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

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

22
  // declare variables for row and column index.
23
  var row_to_read = 201;
24
  var row_to_read_actual;
25

26
  // declare list.
27
  var idList;
28

29
  // get sheet.
30
  var ss    = SpreadsheetApp.getActive();
31
  var sheet = ss.getSheetByName(sheetName);
32

33
  // memorize number of row to read
34
  console.time(`SELECT TOP${row_to_read - 1} id FROM \'${sheetName}\'`);
35
  idList = sheet.getRange(2, column_for_siteBookmark_of_id, row_to_read - 1, 1).getValues();
36
  console.timeEnd(`SELECT TOP${row_to_read - 1} id FROM \'${sheetName}\'`);
37
  idList_formated = listFormated(idList);
38

39
  // warning message. If condition is false, nothing to do.
40
  row_to_read_actual = Number(idList_formated.reduce((a,b)=>Math.max(a,b)));
41
  row_to_read - row_to_read_actual <= 2 ? console.warn(warningMessage) : false;
42
  if(row_to_read_actual >= row_to_read - 1){
43
    console.error(errorMessage);
44
    return 0;
45
  }
46
  return row_to_read_actual;
47
}
48

49
/**
50
 * Get list to set image onto Google Spreadsheet.
51
 *
52
 * @return [[number], [string]] - recordId and fileFullname
53
 * @customfunction
54
 */
55
function getListToSetImage() {
56
  // declare variables for prepare.
57
  var ss,
58
    sheet;
59
  var bookmarkList;
60
  ss    = SpreadsheetApp.getActive();
61
  sheet = ss.getSheetByName(sheetName2nd);
62

63
  var row_to_read_actual = get_row_to_read_actual_in_GSS(sheetName2nd);
64
  if(row_to_read_actual == 0){
65
    console.error(errorMessageList[0]);
66
    return false;
67
  }
68

69
  // Get files' name and fullname to JSON format.
70
  var folder = DriveApp.getFolderById(iconFolderId),
71
      files  = folder.getFiles(),
72
      file;
73
  var fileNameList            = [],
74
      fileFullNameList        = [],
75
      fileNameDict            = {},
76
      iconInsertingRecordList = [];
77
  while(files.hasNext()) {
78
    file     = files.next();
79
    fileNameList.push(file.getName().match(/([^/]*)\./)[1]);
80
    fileFullNameList.push(file.getName())
81
  }
82
  fileNameDict['name']     = fileNameList;
83
  fileNameDict['fullName'] = fileFullNameList;
84

85
  // Get siteName list from GSS and extract record that fileName and siteName are same.
86
  bookmarkList = sheet.getRange(row_for_siteBookmark_of_firstRecord, column_for_siteBookmark_of_id, row_to_read_actual, column_for_siteBookmark_of_icon).getValues();
87
  bookmarkList.forEach(function(record) {
88
    if (record[column_for_siteBookmark_of_icon - 1] == '') {
89
      for (let i = 0; i < fileNameDict['name'].length; i++){
90
        if (fileNameDict['name'][i] == record[column_for_siteBookmark_of_siteName - 1]) {
91
          iconInsertingRecordList.push([record[column_for_siteBookmark_of_id - 1], fileNameDict['fullName'][i]]);
92
          break;
93
        }
94
      }
95
    }else{
96
      // nothing to do.
97
    }
98
  });
99

100
  return iconInsertingRecordList;
101
}
102

103
function serializeArray(targetArray){
104
  var onlyStringArray = [];
105
  targetArray.forEach(item => {
106
    onlyStringArray.push(`[${String(item)}]`);
107
  });
108
  var serializedArray = onlyStringArray.join(',');
109
  return serializedArray;
110
}
111

112
function setImageFromList(listToSetImage) {
113
  // declare variables.
114
  var file;
115
  var iconUrl,
116
    formattedUrl;
117
  var ss    = SpreadsheetApp.getActive();
118
  var sheet = ss.getSheetByName(sheetName2nd);
119

120
  console.info(`INFO: listToSetImage is${serializeArray(listToSetImage)}`);
121

122
  // In this case, not in-cell image but OverGridImage...
123
  // var file = DriveApp.getFolderById(iconFolderId).getFilesByName('note.png').next();
124
  // var fileBlob = file.getBlob();
125
  // var insertedImage = sheet.insertImage(fileBlob, column_for_siteBookmark_of_icon, 4);
126
  // insertedImage.setAnchorCell(sheet.getRange(4, column_for_siteBookmark_of_icon));
127

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

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