GoogleSpreadSheetでupsertするには

上記の様に、スプレッドシートである列(今回はB列)を主キーと定義し
あるデータを追加する時に主キーが同じ行があれば上書き、なければ追記をしたい。

作ったコードは以下の通り。最終的にはDataUpsertUtilクラスのupsertDataメソッドを叩けば何でも良い。
B列はタイムスタンプにしてなどの細かい設定はDataUpsertUtilクラスのコンストラクタで定義する。
データのupsert完了後、列ごとに書式を上書きしているので 日付型や桁数指定の小数の表示が乱れない事が最大のウリ。

使い方.gs

//@ts-check
const hoge = "無題2"
function doPost(context) {
  console.log(`doPost が実行された`);
  console.time("doPost");
  try {
    const jsonObj = JSON.parse(context.postData.contents);
    // ここでデータ登録する
    const output = ContentService.createTextOutput(JSON.stringify({ result: "ok", context: context }));
    output.setMimeType(ContentService.MimeType.JSON);
    console.timeEnd("doPost");
    return output;
  } catch (e) {
    const output = ContentService.createTextOutput(JSON.stringify({ error: `${e}`, context: context }));
    output.setMimeType(ContentService.MimeType.JSON);
    console.error(e);
    console.timeEnd("doPost");
    return output;
  }
}
function fSujiFormat(contest, c2) {
  console.log(`fSujiFormat が実行された`);
  console.log(contest);
  console.log(`プロパティ一覧:${JSON.stringify({ ...contest })}`);
  return "ok";
}
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('マイメニュー 無題2')
    .addItem('あいてむ', 'funcTest2')
    .addToUi();
}
function funcTest2() {
  const appendData = [];
  const processStartTime = Date.now();
  const pKeySet = new Set();
  for (let i = 0; i < 1000; i++) {
    const randStr = `id-${String(Math.floor(Math.random() * 10000)).padStart(5, "0")}`;
    if(pKeySet.has(randStr)){
      continue;
    }
    pKeySet.add(randStr);
    appendData.push([
      `${processStartTime}-${i}`,
      randStr,
      i,
      processStartTime,
      i,
      i / 100 + 100
    ]);
  }
  const upsertResult = new DataUpsertUtil(
    "B",
    [
      { col: "C", format: "0" },
      { col: "D", format: "yyyy/MM/dd HH:mm:ss", isTime: true },
      { col: "E", format: `0 "人日"` },
      { col: "F", format: "0.00" },
    ],
    {
      createCol: "G",
      updateCol: "H",
    }
  ).upsertData("シート1", appendData);
  SpreadsheetApp.getUi().alert(`ok\n${JSON.stringify(upsertResult)}`);
}

DataUpsertUtil.gs

//@ts-check
class DataUpsertUtil {
  /**
   * データ列はA列から任意の列まで。 upsertData 時のそれぞれの行の数で動的に反映される
   * 日付列はデータ列より右である事。
   * データ作成日、データ更新日の日付はコンストラクタを呼んだ瞬間の時刻。
   * 同じコンストラクタで2回upsertData を呼ぶことは想定していない
  * @param {string} primaryKeyCol
  * @param {{col:string,format:string,isTime?:true}[]} colFormatList
  * @param {{createCol?:string,updateCol?:string}} dateKey
  */
  constructor(
    primaryKeyCol,
    colFormatList,
    dateKey,
  ) {
    const pKeyCol = this._parseA1Cell(primaryKeyCol);
    /** @type {number|null} */
    this.createColNum = null;
    /** @type {number|null} */
    this.updateColNum = null;
    if (dateKey.createCol != null) {
      this.createColNum = this._parseA1Cell(dateKey.createCol);
    }
    if (dateKey.updateCol != null) {
      this.updateColNum = this._parseA1Cell(dateKey.updateCol);
    }
    if (this.createColNum != null && this.createColNum == this.updateColNum) {
      throw new Error(`データ追加日と更新日に同じ列を指定しようとした`);
    }
    /** @type {Map<number,{col:string,format:string,isTime?:true}>} */
    const formatMap = new Map();
    for (const f of colFormatList) {
      const fCol = f.col;
      if (fCol == null) { throw new Error(); }
      const fColNum = this._parseA1Cell(fCol);
      if (fColNum == pKeyCol) {
        throw new Error(`主キーにフォーマットを指定しようとしました。主キーは絶対文字列です`);
      }
      if (fColNum == this.createColNum) {
        throw new Error(`データ追加日の列にフォーマットを指定しようとした`);
      }
      if (fColNum == this.updateColNum) {
        throw new Error(`データ更新日の列にフォーマットを指定しようとした`);
      }
      if (formatMap.has(fColNum)) {
        throw new Error(`フォーマット定義に重複がある`);
      }
      formatMap.set(fColNum, f);
    }
    this.pKeyCol = pKeyCol; //number
    this.formatMap = formatMap; //Map<number,obj>
    this.timeColNumberSet = new Set([...this.formatMap].filter(v => {
      return v[1].isTime == true;
    }).map(v => v[0]));
    this.transactionDateMs = Date.now();
  }
  /**
   * データ登録。
   * 主キーはコンストラクタのprimaryKeyColから自動的に判別される。
   * フォーマット定義が無い列は自動的に文字列扱いになる
   * フォーマット定義が日付で、データが数字の場合はUnixTimeMsとして扱う
  * @param {string} sheetName
  * @param {(string|number|Date)[][]} dataArrayNest
   */
  upsertData(sheetName, dataArrayNest) {
    const transactionDateMs = Date.now();
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    if (sheet == null) { throw new Error("シートがありません"); }
    // dataArrayNestの中自体に主キーの重複が無いか調べる
    {
      /** @type {Set<string>} */
      const checkedPKeySet = new Set();
      dataArrayNest.forEach((line, count) => {
        const pKeyRaw = line[this.pKeyCol - 1];
        if (typeof pKeyRaw != "string") {
          throw new Error(`主キーは文字列型である必要があります, type=${typeof pKeyRaw}, count=${count}`);
        }
        if (checkedPKeySet.has(pKeyRaw)) {
          throw new Error(`主キーの文字列が重複しています, pKeyRaw=[${pKeyRaw}], count=${count}`);
        }
        checkedPKeySet.add(pKeyRaw);
      })
    }
    const pKeyLineMap = this._getPKeyLines(sheet);
    console.log(`主キーの一覧: ${JSON.stringify([...pKeyLineMap])}`)
    // 行ごとに更新か追記かを判断する
    /** @type {{line:number,data:any}[]} */
    const updateDatas = [];
    const insertDatas = [];
    for (const data of dataArrayNest) {
      const pKeyStr = data[this.pKeyCol - 1].toString();
      const updateLine = pKeyLineMap.get(pKeyStr);
      if (updateLine != null) {
        updateDatas.push({ line: updateLine, data: data });
      } else {
        insertDatas.push(data);
      }
    }
    // 更新する
    for (const updateData of updateDatas) {
      this._updateData(sheet, updateData.line, updateData.data);
    }
    // 追記する
    for (const line of insertDatas) {
      sheet.appendRow(this._convertFormatedData1Line(line, true));
    }
    this._resetNumberFormat(sheet);
    console.log(`upsert完了`);
    return {
      update: updateDatas.length,
      append: insertDatas.length,
      timeMs:Date.now() - transactionDateMs,
    };
  }
  _getPKeyLines(sheet) {
    // B列の「最終行」まで（1行目はヘッダなら 2 にする）
    const dataRange = sheet.getDataRange();
    const startRow = 1;
    const lastRow = dataRange.getLastRow();
    const colWidth = 1;
    const values = sheet.getRange(startRow, this.pKeyCol, lastRow - startRow + 1, colWidth).getDisplayValues(); // col=2(B)
    /** @type {Map<string,number>} */
    const map = new Map();

    for (let i = 0; i < values.length; i++) {
      const row = startRow + i;
      const v = values[i][0];

      // 文字列化（空/NULL対策）
      const key = (v ?? "").toString().trim();
      if (key === "") {
        continue
      }

      // 同じkeyが複数ある場合：最初の行を保持（上書きしたいならこのifを消す）
      if (map.has(key)) {
        throw new Error(`Keyが重複しています. 最初の行を更新します. 行数=${i + 1}, key=[${key}]`);
      } else {
        map.set(key, row);
      }
    }
    return map;
  }
  /**
   * データを追加する。主キーが重複しているデータは全てupdate済みのはず。
  * @param {number} lineNum
  * @param {(string|number|Date)[]} data
   */
  _updateData(sheet, lineNum, data) {
    const formattedData = this._convertFormatedData1Line(data, false);
    const beforeLength = formattedData.length;
    if (this.updateColNum != null) {
      formattedData.length = Math.min(this.updateColNum - 1, formattedData.length);
    }
    if (this.createColNum != null) {
      formattedData.length = Math.min(this.createColNum - 1, formattedData.length);
    }
    const afterLength = formattedData.length;
    if (beforeLength != afterLength) {
      console.warn(`データ追記時に列の数が多すぎて日付列を上書きしようとした`);
    }
    sheet.getRange(lineNum, 1, 1, formattedData.length).setValues([formattedData]);
    if (this.updateColNum != null) {
      sheet.getRange(lineNum, this.updateColNum, 1, 1).setValues([[new Date(this.transactionDateMs)]]);
    }
  }
  /**
   * 1行分のデータをフォーマットする
   * @param {(string|number|Date)[]} line
   * @param {bool} withTimestamp
   */
  _convertFormatedData1Line(line, withTimestamp) {
    const appendData = line.map((item, index) => {
      if (this.timeColNumberSet.has(index + 1)) {
        if (typeof item == "number") {
          return new Date(item); // ミリ秒とする
        }
      }
      return item;
    });
    if (withTimestamp) {
      // データの配列はB列までしかないけど、タイムスタンプ系がZ列だった場合は空文字列のセルを追加しないといけない
      while (true) {
        if (this.createColNum == null && this.updateColNum == null) {
          break;
        }
        let isOk = true;
        if (this.createColNum != null && appendData.length < this.createColNum) {
          isOk = false;
        }
        if (this.updateColNum != null && appendData.length < this.updateColNum) {
          isOk = false;
        }
        if (isOk) {
          break;
        }
        appendData.push("");
      }
      if (this.createColNum != null) {
        appendData[this.createColNum - 1] = new Date(this.transactionDateMs);
      }
      if (this.updateColNum != null) {
        appendData[this.updateColNum - 1] = new Date(this.transactionDateMs);
      }
    }
    return appendData;
  }
  _resetNumberFormat(sheet) {
    const range = sheet.getDataRange();
    const numRows = range.getNumRows();
    const numCols = range.getNumColumns();
    // 2次元のフォーマット配列を作る（サイズは range と同じ）
    const formats = Array.from({ length: numRows }, () =>
      Array.from({ length: numCols }, (_, c) => {
        if (this.updateColNum == c + 1) {
          return "yyyy/MM/dd HH:mm:ss";
        } else if (this.createColNum == c + 1) {
          return "yyyy/MM/dd HH:mm:ss";
        }
        const formatDef = this.formatMap.get(c + 1)
        if (formatDef == null) { return "@"; }
        return formatDef.format;
      })
    );
    range.setNumberFormats(formats);
  }
  /**
   * return: A → 1
   */
  _parseA1Cell(a1) {
    const m = a1.trim().match(/^([A-Za-z]+)/);
    if (!m) throw new Error(`Invalid A1 cell: ${a1}`);
    const letters = m[1].toUpperCase();
    let col = 0;
    for (let i = 0; i < letters.length; i++) {
      col = col * 26 + (letters.charCodeAt(i) - 64); // 'A'->1 ... 'Z'->26
    }
    return col;
  }
}