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

2707 語
14 分
【GAS、Google Spreadsheet】Google Driveに共有したWebサイトのURLをGoogleスプシに転記する。
2021-12-31
Code
gas
/
spreadsheet
/
googledrive

はじまり#

リサちゃん avatar
リサちゃん
うーん、あの記事ってどこにブックマークしたっけ？ Qiitaだっけ？ Zennだっけ？
リサちゃん avatar
リサちゃん
前にいいなと思った記事がどこかに行ってしまった・・・また調べ直さなければならんじゃないか・・・
135ml avatar
135ml
じゃあ記事の管理を一元化してしまうかあ。気になったWebの記事をGoogleドライブにほいほい放り込んで、スプレッドシートで管理してしまおう。
リサちゃん avatar
リサちゃん
お！それ採用！ さっそく作るのだ！

ツールの概要#

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

  1. Googleドライブにある.txtファイルの内容をGoogleスプレッドシートに転記する。（main()）
  2. 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スプレッドシートに転記する。#

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

  1. MIMEタイプに「text/plain」を選び、対象のGoogleドライブのフォルダから.txtファイルだけを取得する。
  2. そのファイルのファイルIDを取得する。（ファイルIDの配列は最終更新日付順にする。）
  3. ファイルIDから、ファイルの最終更新日付、タイトル、URLを取得する。
  4. Googleスプレッドシートに速く書き込めるように、配列の形を加工する。
  5. Googleスプレッドシートに情報を書き込む。
  6. 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;
}

おしまい#

リサちゃん avatar
リサちゃん
これで気になった記事が一箇所で管理できるぞ！
135ml avatar
135ml
気になった記事ってそれぞれのサービス内で保存すると、あんまり利便性がないよね。かと言って、ただGoogleドライブに共有しただけだと、わざわざテキストファイルを開いてそのWebページにアクセスしなきゃいけないから面倒いよね。
リサちゃん avatar
リサちゃん
シート上で管理しているから見やすくなったぞぉ

以上になります！

記事を共有

この記事が役に立ったなら、ぜひ他の人と共有してください！

【GAS、Google Spreadsheet】Google Driveに共有したWebサイトのURLをGoogleスプシに転記する。
https://endorphinbath.com/posts/gas-webclip-to-gss/
著者
kinkinbeer135ml
公開日
2021-12-31
ライセンス
CC BY-NC-SA 4.0
【GAS、Google Spreadsheet】ドライブにある画像をセルに表示しまくるツール
【GAS、Google Spreadsheet】Twitterにランダムなツイートを投稿するBotを作る
関連記事 スマート
1
【GAS、Google Spreadsheet】Googleドライブ内の指定したフォルダに所定のファイルをコピーするスクリプトです
Code GoogleDriveの指定のフォルダにファイルをコピーするツールを作りました。何かWebの記事をGoogleドキュメントにクリップしたり、指定のフォルダにメモ書きやシートを作成したい場合に役立つかと思います。
2
【GAS、Google Spreadsheet】Googleドライブ内の指定したファイルをそれぞれ好みの名前に一気にリネームするスクリプトです
Code GoogleDriveの指定のファイルを一括でリネームできるツールを作りました。既に沢山作ってしまったファイルの命名規則を変えたいときなどにご活用下さい。
3
【GAS、Google Spreadsheet】Googleドライブのフォルダに有るファイルを一覧で取得するスクリプトです
Code GoogleDriveの指定のフォルダにあるファイルを一覧で取得します。Googleドライブを整理したい時に役立つツールになるかと思います。
4
【GAS、Google Spreadsheet】Googleドライブのルートフォルダに有るフォルダを一覧で取得するスクリプトです
Code GoogleDriveのルートフォルダにあるフォルダを一覧で取得します。Googleドライブを整理したい時に役立つツールになるかと思います。
5
【GAS、Google Spreadsheet】ブログに使用した画像をGoogleドライブで管理するために書いたスクリプト
Code 僕は、既に公開した記事で使用したファイルをGoogleドライブの所定のフォルダに保存しています。その画像を完了済みのフォルダに定期的に移動してくれるスクリプトを作りました。
ランダム記事 ランダム
Profile Image of the Author
kinkinbeer135ml
SIerをやめて、プログラミングを勉強しています。※Amazonアソシエイトに参加しています。
お知らせ
私のブログへようこそ！これはサンプルのお知らせです。
音楽
カバー

音楽

再生中なし

0:00 0:00
歌詞なし
カテゴリ
タグ
サイト統計
記事
287
カテゴリー
8
タグ
93
総文字数
486,174
運用日数
0 日
最終活動
0 日前
© 2026 kinkinbeer135ml. All Rights Reserved. / RSS / Sitemap
Powered by Astro & Firefly

目次