【GAS、Google Spreadsheet】Google Driveに共有したWebサイトのURLをGoogleスプシに転記する。

はじまり
ツールの概要
ソース全体
ツールの動き①：Googleドライブにある.txtファイルの内容をGoogleスプレッドシートに転記する。
ツールの動き②：GoogleスプレッドシートにWebサイトのtitleが記載されていない行のtitleを埋める。
1. 読み取る行数を決定する。
2. 空白行の配列を取得する。
おしまい

はじまり

リサちゃん

うーん、あの記事ってどこにブックマークしたっけ？

Qiitaだっけ？ Zennだっけ？

135ml

おお、どうした〜？

リサちゃん

前にいいなと思った記事がどこかに行ってしまった・・・

また調べ直さなければならんじゃないか・・・

135ml

おーし、じゃあ記事の管理を一元化してしまうかあ。

気になったWebの記事をGoogleドライブにほいほい放り込んで、スプレッドシートで管理してしまおう。

リサちゃん

お！それ採用！　さっそく作るのだ！

135ml

前に作ったものを以下に紹介しまーす。

ツールの概要

本ツールで行える処理は、主に以下の2つです。

Googleドライブにある.txtファイルの内容をGoogleスプレッドシートに転記する。（main()）
GoogleスプレッドシートにWebサイトのtitleが記載されていない行のtitleを埋める。（inputTitlesToEmptyCell()）

1.を実行することで、このようなGoogleドライブのフォルダから、スプレッドシートに転記します。

ソース全体

constants.gs

const temporaryTextFolderId = 'XXXXXXXXXXXXXXXXXXXXXXX';

const sheetNameDisseminating1st = 'まとめ';

const sheetNameOthers1st        = '';

const column_for_id_1st     = 1; // index of 「」column.
const column_for_date_1st   = 2; // index of 「」column.
const column_for_title_1st  = 3; // index of 「」column.
const column_for_url_1st    = 4; // index of 「」column.

const heightHeader1st = 1;

main.gs

// main process
// for utilities (for main process and others)
function getStrRepeatedToMark(repeatStr, repeatNumberToMark=15){
  return repeatStr.repeat(repeatNumberToMark);
};

function getResponseUntilMaxRetries(url, maxRetries){
  // This function is to avoid 403 Forbidden Error.
  const funcName = 'getResponseUntilMaxRetries';
  let res = null;
  for (let i = 0; i < maxRetries; i++) {
    res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
    if (res.getResponseCode() == 200) break;
    res = null;
    console.log(`${funcName}: ${getStrRepeatedToMark('a')}: countOfRetries = ${i}`);
    Utilities.sleep(5000);
  }
  return res;
}

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} Number of record
 * @customfunction
 */
function get_row_to_read_actual_in_GSS(sheetName, column_for_id, row_to_read) {
  const funcName = 'get_row_to_read_actual_in_GSS';

  // declare list for warning message.
  const warningMessage = 'Warning: Number of row passing over \"row_to_read\". Tweak me.';
  const errorMessage   = 'RowIndexOutOfBoundsError: Number of row reached \"row_to_read\". Tweak me.';

  // declare variables for row and column index.
  let row_to_read_actual;

  // declare list.
  let idList;

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

  // memorize number of row to read
  console.time(`${funcName}: SELECT TOP ${row_to_read - 1} id FROM \'${sheetName}\'`);
  idList = sheet.getRange(2, column_for_id, row_to_read - 1, 1).getValues();
  console.timeEnd(`${funcName}: 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;
  }
  console.log(`${funcName}: row_to_read_actual is ${row_to_read_actual}`);
  return row_to_read_actual;
}

function isExistFileViaFileIdInFolder(fileId, folderId, mimeType) {
  let folder = DriveApp.getFolderById(folderId);
  let files = folder.getFilesByType(mimeType);
  let nowFile = '';
  while (files.hasNext()) {
    nowFile = files.next();
    if (nowFile.getId() === fileId) {
      return true;
    }
  }
  return false;
}

// for main execution
function removeFilesFromFileIdArray(fileIdArray, mimeType){
  const funcName = 'removeFilesFromFileIdArray';
  let isRemovedAll = true;
  console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
  console.log(fileIdArray)
  fileIdArray.forEach(id => {
    DriveApp.getFileById(id).setTrashed(true);
  })
  console.log(`${funcName}: ${getStrRepeatedToMark('b')}: `);

  let isExistFileArray = [];
  fileIdArray.forEach(id => {
    let isExistFile = isExistFileViaFileIdInFolder(id, temporaryTextFolderId, mimeType);
    console.log(`${funcName}: ${getStrRepeatedToMark('c')}: `);
    console.log(`isExistFile is ${isExistFile}`);
    isExistFileArray.push(isExistFile);
    if(isExistFile === true){
      isRemovedAll = false;
    }
  })
  console.log(`${funcName}: ${getStrRepeatedToMark('d')}: `);
  console.log(`isRemovedAll is ${isRemovedAll}`);

  if(isRemovedAll === false){
    let removeResultArray = [];
    for(let i = 0; i < fileIdArray.length; i++){
      removeResultArray.push([fileIdArray[i], isExistFileArray[i], DriveApp.getFileById(fileIdArray[i]).getName()]);
    }
    console.log(`${funcName}: ${getStrRepeatedToMark('e')}: `);
    console.log(removeResultArray);
  }

  return isRemovedAll;
}

function writeInfoToGSS(obj) {
  const funcName = 'writeInfoToGSS';

  // evaluate writable or not.
  console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
  console.log(obj)

  let arrayOfKey = Object.keys(obj);
  console.log(`${funcName}: ${getStrRepeatedToMark('b')}: `);
  console.log(arrayOfKey);
  console.log(obj[arrayOfKey[0]]);

  let isWritten = false;
  if(obj[arrayOfKey[0]][0].length === 0){
    console.log(`${funcName}: ${getStrRepeatedToMark('c')}: `);
    console.log("records to write is nothing.");
    return isWritten;
  }
  console.log(`${funcName}: ${getStrRepeatedToMark('d')}: `);

  // decide start row to write info
  const row_to_read_actual = get_row_to_read_actual_in_GSS(sheetNameDisseminating1st, 1, 801);
  if(row_to_read_actual === 0){
    console.log(`${funcName}: ${getStrRepeatedToMark('e')}: `);
    console.log("row_to_read_actual: " + row_to_read_actual);
    return isWritten;
  }
  const startRow = row_to_read_actual + heightHeader1st + 1;
  console.log(`${funcName}: ${getStrRepeatedToMark('f')}: `);
  console.log("startRow: " + startRow);

  // write info(id)
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet       = spreadsheet.getSheetByName(sheetNameDisseminating1st);
  console.time(`INSERT \'${sheetNameDisseminating1st}\'Sheet SET \'id\' = *;`);
  sheet.getRange(startRow, column_for_id_1st, obj[arrayOfKey[0]].length, 1).setFormula('=ROW()-1');
  console.timeEnd(`INSERT \'${sheetNameDisseminating1st}\'Sheet SET \'id\' = *;`);
  console.log(`${funcName}: ${getStrRepeatedToMark('g')}: `);

  // write info(except id)
  console.time(`UPSERT \'${sheetNameDisseminating1st}\'Sheet SET \'clipDate\'~\'URL\' = * WHERE \'id\' = ${startRow - 1}~${startRow - 1 + obj[arrayOfKey[0]].length};`);
  for(let i = 0; i < arrayOfKey.length; i++){
    console.log(`${funcName}: ${getStrRepeatedToMark('h')}: `);
    console.log(`i : ${arrayOfKey[i]}`);
    console.log(`obj[arrayOfKey[i]]: ${obj[arrayOfKey[i]]}`);
    sheet.getRange(startRow, arrayOfKey[i], obj[arrayOfKey[i]].length, obj[arrayOfKey[i]][0].length).setValues(obj[arrayOfKey[i]]);
  }
  console.timeEnd(`UPSERT \'${sheetNameDisseminating1st}\'Sheet SET \'clipDate\'~\'URL\' = * WHERE \'id\' = ${startRow - 1}~${startRow - 1 + obj[arrayOfKey[0]].length};`);
  console.log(`${funcName}: ${getStrRepeatedToMark('i')}: `);

  isWritten = true;
  return isWritten;
}

function integrateObjToWriteGSS(...objects) {
  const funcName = 'integrateObjToWriteGSS';
  console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
  console.log(objects);
  
  // Length of object in objects must be same each other: Start ----------------------------------------------------
  console.log(Object.keys(objects))
  let arrayOfKey = [];
  for(let j = 0; j < objects.length; j++){
    arrayOfKey.push(Object.keys(objects[j])[0]);
  }
  let lengthOfObject1;
  let lengthOfObject2;
  console.log(`${funcName}: ${getStrRepeatedToMark('b')}: `);
  console.log("arrayOfKey: [ " + arrayOfKey + " ]");

  let objWithoutArray = {};
  objects.forEach(obj => {
    for (let j in obj){
      objWithoutArray[j] = obj[j];
    }
  });
  console.log(`${funcName}: ${getStrRepeatedToMark('c')}: `);
  console.log(objWithoutArray);

  for(let j = 0; j < arrayOfKey.length; j++){
    lengthOfObject1 = objWithoutArray[arrayOfKey[j]].length;
    console.log('L1: ' + lengthOfObject1)

    for(let k = j + 1; k < arrayOfKey.length; k++){
      lengthOfObject2 = objWithoutArray[arrayOfKey[k]].length;
      console.log('L2: ' + lengthOfObject2)
      
      if(lengthOfObject1 !== lengthOfObject2){
        console.log(`${funcName}: ${getStrRepeatedToMark('d')}: `);
        console.log(`Length of objects is different: lengthOfObject1: ${lengthOfObject1} != lengthOfObject2: ${lengthOfObject2}`);
        return {'2': [[]]};
      }

    }
  }

  console.log(`${funcName}: ${getStrRepeatedToMark('e')}: `);
  // Length of object in objects must be same each other: End ----------------------------------------------------

  // Data processing: Start --------------------------------------------------------------------------------------
  let arrayOfColumnsIndex = [];
  let i = 0;
  let indexOfArrayOfColumnsIndex = 0;
  while(i < objects.length){
    console.log(objects[i]);
    if(i === 0){
      for (let j in objects[i]){
        arrayOfColumnsIndex.push([j]);
      }
    }
    if(i > 0){
      let k = 0;
      let l = 0;
      for (let j in objects[i]){
        k = Number(JSON.parse(JSON.stringify(j)));
      }
      for (let j in objects[i-1]){
        l = Number(JSON.parse(JSON.stringify(j)));
      }
      if(k - l === 1){
        for (let j in objects[i]){
          arrayOfColumnsIndex[indexOfArrayOfColumnsIndex].push(j);
        }
      }else{
        indexOfArrayOfColumnsIndex++;
        for (let j in objects[i]){
          arrayOfColumnsIndex.push([j]);
        }
      }
    }
    console.log(`${funcName}: ${getStrRepeatedToMark('f')}: `);
    console.log(objects.length);
    i++;
  }
  console.log(`${funcName}: ${getStrRepeatedToMark('g')}: `);
  console.log(arrayOfColumnsIndex);

  let recordsArray = [];
  console.log(`${funcName}: ${getStrRepeatedToMark('h')}: `);
  console.log(objects[0])
  arrayOfColumnsIndex.forEach(columnsIndex => {
    let records = [];
    for (let data of Object.values(objects[0])){
      for (let j = 0; j < data.length; j++){
        let record = [];
        columnsIndex.forEach(columnIndex => {
          record.push(objWithoutArray[columnIndex][j])
        });
        records.push(record);
      }
    }
    recordsArray.push(records);
  });
  
  console.log(`${funcName}: ${getStrRepeatedToMark('i')}: `);
  console.log(recordsArray);

  let objRecordsArray = {};
  for(let i = 0; i < arrayOfColumnsIndex.length; i++){
    objRecordsArray[arrayOfColumnsIndex[i][0]] = recordsArray[i]
  }
  console.log(`${funcName}: ${getStrRepeatedToMark('j')}: `);
  console.log(objRecordsArray);

  // Data processing: End --------------------------------------------------------------------------------------

  // Data processing in the case that record is empty: Start ----------------------------------------------------
  const recordsForWriting = objRecordsArray[Object.keys(objRecordsArray)[0]];
  console.log(`${funcName}: ${getStrRepeatedToMark('k')}: `);
  console.log(`recordsForWriting: ${recordsForWriting}`);
  if(recordsForWriting.length === 0){
    return {'2': [[]]};
  }
  console.log(`${funcName}: ${getStrRepeatedToMark('l')}: `);
  // Data processing in the case that record is empty: End ----------------------------------------------------

  return objRecordsArray;
}

function applyColumnIndexToInfoArray(columnIndex, infoArray) {
  const funcName = 'applyColumnIndexToInfoArray';
  const indexToString = String(columnIndex)
  const appliedObj = {};
  appliedObj[indexToString] = infoArray;
  console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
  return appliedObj;
}

function getUrlArrayFromFileIdArray(fileIdArray) {
  const funcName = 'getUrlArrayFromFileIdArray';
  let urls = [];
  let file;
  fileIdArray.forEach(id => {
    file = DriveApp.getFileById(id);
    let text = file.getBlob().getDataAsString("utf-8");
    urls.push(text);
  });  
  console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
  console.log(urls);
  return urls;
}

function getFileNameArrayFromFileIdArray(fileIdArray) {
  const funcName = 'getFileNameArrayFromFileIdArray';
  let fileNames = [];
  let file;
  fileIdArray.forEach(id => {
    file = DriveApp.getFileById(id);
    fileNames.push(file.getName());
  });
  console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
  return fileNames;
}

function getSiteTitleArrayFromUrlArray(urlArray) {
  const funcName = 'getSiteTitleArrayFromUrlArray';
  let siteTitles = [];
  let file;
  urlArray.forEach(url => {
    let res = getResponseUntilMaxRetries(url, 5);
    if (res === null){
      return [];
    }
    let contentText = res.getContentText('UTF-8');
    let siteTitle = String(Parser.data(contentText).from('<title>').to('</title>').iterate());
    siteTitle = siteTitle.replace(/\r?\n/g, '');
    siteTitles.push(siteTitle);
  });
  console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
  console.log(siteTitles);
  return siteTitles;
}

function getDateArrayFromFileIdArray(fileIdArray) {
  const funcName = 'getDateArrayFromFileIdArray';
  let dates = [];
  let file;
  fileIdArray.forEach(id => {
    file = DriveApp.getFileById(id);
    dates.push(Utilities.formatDate(file.getLastUpdated(), "JST", "yyyy/MM/dd"));
  });
  console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
  console.log(dates);
  return dates;
}

function getFileIdArrayInTheFolder(folderId, mimeType='text/plain') {
  const funcName = 'getTextFileIdArrayInTheFolder';
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFilesByType(mimeType);
  let fileInfoArray = [];
  let fileIdArray = [];

  while(files.hasNext()) {
    let file = files.next();
    fileInfoArray.push([file.getId(), Utilities.formatDate(file.getLastUpdated(), "JST", "yyyy/MM/dd")]);
  }
  console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
  console.log(fileInfoArray);

  fileInfoArray.sort(function(a,b){
    if(a[1] < b[1]) return -1;
    if(a[1] > b[1]) return 1;
    return 0;
  });
  console.log(`${funcName}: ${getStrRepeatedToMark('b')}: `);
  console.log(fileInfoArray);

  fileInfoArray.forEach(info => {
    fileIdArray.push(info[0]);
  });

  console.log(`${funcName}: ${getStrRepeatedToMark('c')}: `);
  console.log(fileIdArray);
  return fileIdArray;
}

// main execution for main process.
function main() {
  const mimeType = 'text/plain';
  const textFileIdArray = getFileIdArrayInTheFolder(temporaryTextFolderId, mimeType); // できた
  let dateArray      = getDateArrayFromFileIdArray(textFileIdArray); // できた
  let urlArray       = getUrlArrayFromFileIdArray(textFileIdArray); // できた
  let siteTitleArray = getSiteTitleArrayFromUrlArray(urlArray); // できた

  // for test
  // dateArray      = [0,1,2,4];
  // siteTitleArray = [2,4,8,9];
  // urlArray       = [5,7,1,5];
  
  const dateObj  = applyColumnIndexToInfoArray(column_for_date_1st, dateArray); // できた
  const titleObj = applyColumnIndexToInfoArray(column_for_title_1st, siteTitleArray); // できた
  const urlObj   = applyColumnIndexToInfoArray(column_for_url_1st, urlArray); // できた
  const integratedObj = integrateObjToWriteGSS(dateObj, titleObj, urlObj); // できた
  const isWritten    = writeInfoToGSS(integratedObj);
  let isRemovedAll = false;
  if(isWritten === true){
    isRemovedAll = removeFilesFromFileIdArray(textFileIdArray, mimeType);
  }
}


// the other process (part 1)
// for utilities (for other process)
function inputValuesToEmptyCell(sheetName, valueArray, rowArray, inputingColumn) {
  const funcName = 'inputValuesToEmptyCell';
  let ss = SpreadsheetApp.getActive();
  let sheet = ss.getSheetByName(sheetName);
  let isInputed = false;
  for(let i = 0; i < valueArray.length; i++){
    sheet.getRange(rowArray[i], inputingColumn, 1, 1).setValue(valueArray[i]);
    console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
    isInputed = true;
  }
  console.log(`${funcName}: ${getStrRepeatedToMark('b')}: `);
  console.log(isInputed);

  return isInputed;
}

function getValueArrayFromRowArray(sheetName, rowArray, targetColumn){
  const funcName = 'getValueArrayFromRowArray';
  let ss = SpreadsheetApp.getActive();
  let sheet = ss.getSheetByName(sheetName);
  let valueArray = [];
  rowArray.forEach(row => {
    valueArray.push(sheet.getRange(row, targetColumn, 1, 1).getValue());
  })
  console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
  console.log(valueArray);
  return valueArray;
}

function getRowsEmptyCell(sheetName, targetColumn, row_to_read){
  const funcName = 'getRowsEmptyCell';
  // get sheet.
  let ss = SpreadsheetApp.getActive();
  let sheet = ss.getSheetByName(sheetName);
  const targetValueArray = sheet.getRange(heightHeader1st, targetColumn, row_to_read, 1).getValues();
  console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
  console.log(targetValueArray);

  let rowArray = [];
  for(let i = 0; i < targetValueArray.length; i++){
    if (targetValueArray[i][0] === ''){
      rowArray.push(i+1);
    }
  }
  console.log(`${funcName}: ${getStrRepeatedToMark('b')}: `);
  console.log(rowArray);
  return rowArray;
}

// main execution for the other process.
function inputTitlesToEmptyCell(){
  const row_to_read_actual = get_row_to_read_actual_in_GSS(sheetNameDisseminating1st, 1, 801);
  const rowArray = getRowsEmptyCell(sheetNameDisseminating1st, column_for_title_1st, row_to_read_actual + 1);
  const urlArray = getValueArrayFromRowArray(sheetNameDisseminating1st, rowArray, column_for_url_1st);
  const siteTitleArray = getSiteTitleArrayFromUrlArray(urlArray);
  const isInputed = inputValuesToEmptyCell(sheetNameDisseminating1st, siteTitleArray, rowArray, column_for_title_1st);
}

ツールの動き①：Googleドライブにある.txtファイルの内容をGoogleスプレッドシートに転記する。

ツールの動き①の動きは以下になります。

MIMEタイプに「text/plain」を選び、対象のGoogleドライブのフォルダから.txtファイルだけを取得する。
そのファイルのファイルIDを取得する。（ファイルIDの配列は最終更新日付順にする。）
ファイルIDから、ファイルの最終更新日付、タイトル、URLを取得する。
Googleスプレッドシートに速く書き込めるように、配列の形を加工する。
Googleスプレッドシートに情報を書き込む。
Googleスプレッドシートに書き込んだら、書き込んだファイルを削除する。

以下、要所だけピックアップします。

2.　そのファイルのファイルIDを取得する。（ファイルIDの配列は最終更新日付順にする。）

以下が該当のソースになります。

ファイルIDと最終更新日付が何レコードか入った配列を作って最終更新日付でその配列を昇順でソートしています。

function getFileIdArrayInTheFolder(folderId, mimeType='text/plain') {
  const funcName = 'getTextFileIdArrayInTheFolder';
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFilesByType(mimeType);
  let fileInfoArray = [];
  let fileIdArray = [];

  while(files.hasNext()) {
    let file = files.next();
    fileInfoArray.push([file.getId(), Utilities.formatDate(file.getLastUpdated(), "JST", "yyyy/MM/dd")]);
  }
  console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
  console.log(fileInfoArray);

  fileInfoArray.sort(function(a,b){
    if(a[1] < b[1]) return -1;
    if(a[1] > b[1]) return 1;
    return 0;
  });
  console.log(`${funcName}: ${getStrRepeatedToMark('b')}: `);
  console.log(fileInfoArray);

  fileInfoArray.forEach(info => {
    fileIdArray.push(info[0]);
  });

  console.log(`${funcName}: ${getStrRepeatedToMark('c')}: `);
  console.log(fileIdArray);
  return fileIdArray;
}

3.　ファイルIDから、ファイルの最終更新日付、タイトル、URLを取得する。

URLからタイトルを取得する処理があります。

URLが入った配列からそのURLにアクセスします。403 Forbidden Errorを回避するために、最高5回そのURLにアクセスします。そして、通信が成功したらそのtitleタグの中身を取得して、別の配列に格納します。

function getResponseUntilMaxRetries(url, maxRetries){
  // This function is to avoid 403 Forbidden Error.
  const funcName = 'getResponseUntilMaxRetries';
  let res = null;
  for (let i = 0; i < maxRetries; i++) {
    res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
    if (res.getResponseCode() == 200) break;
    res = null;
    console.log(`${funcName}: ${getStrRepeatedToMark('a')}: countOfRetries = ${i}`);
    Utilities.sleep(5000);
  }
  return res;
}

function getSiteTitleArrayFromUrlArray(urlArray) {
  const funcName = 'getSiteTitleArrayFromUrlArray';
  let siteTitles = [];
  let file;
  urlArray.forEach(url => {
    let res = getResponseUntilMaxRetries(url, 5);
    if (res === null){
      return [];
    }
    let contentText = res.getContentText('UTF-8');
    let siteTitle = String(Parser.data(contentText).from('<title>').to('</title>').iterate());
    siteTitle = siteTitle.replace(/\r?\n/g, '');
    siteTitles.push(siteTitle);
  });
  console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
  console.log(siteTitles);
  return siteTitles;
}

4.　Googleスプレッドシートに速く書き込めるように、配列の形を加工する。

この処理を書くのが最も大変でした・・・。コードは割愛しますが、カラム指向でシートへ書き込むようにしたため、レコードがいくら増えてもシートへの書き込み処理の回数が増えません。

例えば、書き込む対象の列インデックスが[2,3,5]だったら、2,3列目に書き込む配列と5列目に書き込む配列をそれぞれ返します。この場合、いくら書き込むレコードが増えても、書き込み回数は2回になります。

ツールの動き②：GoogleスプレッドシートにWebサイトのtitleが記載されていない行のtitleを埋める。

こちらはWebサイトのタイトルをtitleタグから取得して空白の空白の行に埋める処理となっています。

また、定期処理として動かさず、ある程度増えたら実行するスクリプトとして扱っています。

以下、主な処理をピックアップします。

読み取る行数を決定する。

get_row_to_read_actual_in_GSS()という処理で、空白があるかどうかを判断する対象の行を取得します。前項の行数だと「113」が返ってきます。

/**
 * Get number of record in Google Spreadsheet.
 *
 * @param {"bookmarkSites"} sheetName - Name of sheet that you wanna know number of record.
 * @return {number} Number of record
 * @customfunction
 */
function get_row_to_read_actual_in_GSS(sheetName, column_for_id, row_to_read) {
  const funcName = 'get_row_to_read_actual_in_GSS';

  // declare list for warning message.
  const warningMessage = 'Warning: Number of row passing over \"row_to_read\". Tweak me.';
  const errorMessage   = 'RowIndexOutOfBoundsError: Number of row reached \"row_to_read\". Tweak me.';

  // declare variables for row and column index.
  let row_to_read_actual;

  // declare list.
  let idList;

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

  // memorize number of row to read
  console.time(`${funcName}: SELECT TOP ${row_to_read - 1} id FROM \'${sheetName}\'`);
  idList = sheet.getRange(2, column_for_id, row_to_read - 1, 1).getValues();
  console.timeEnd(`${funcName}: 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;
  }
  console.log(`${funcName}: row_to_read_actual is ${row_to_read_actual}`);
  return row_to_read_actual;
}

空白行の配列を取得する。

実際に空白になっている行のインデックスを取得します。

この処理が終わったら、後は前述の処理と同様にカラム指向にシートにtitleタグの値を書き込んでいきます。

function getRowsEmptyCell(sheetName, targetColumn, row_to_read){
  const funcName = 'getRowsEmptyCell';
  // get sheet.
  let ss = SpreadsheetApp.getActive();
  let sheet = ss.getSheetByName(sheetName);
  const targetValueArray = sheet.getRange(heightHeader1st, targetColumn, row_to_read, 1).getValues();
  console.log(`${funcName}: ${getStrRepeatedToMark('a')}: `);
  console.log(targetValueArray);

  let rowArray = [];
  for(let i = 0; i < targetValueArray.length; i++){
    if (targetValueArray[i][0] === ''){
      rowArray.push(i+1);
    }
  }
  console.log(`${funcName}: ${getStrRepeatedToMark('b')}: `);
  console.log(rowArray);
  return rowArray;
}