🥶

コンサル前のクライアント情報分析（ahrefsのcsvファイル）をGASで自動整形したった話

2024/11/13に公開

GAS

Google Apps Script

ahrefs

tech

 1. はじめにこんにちは。今回は、Google Apps Script (GAS) を使って、特定の条件を満たすシートに対して複雑な自動整形処理を行う方法について紹介します。最近、「organic」という文字列を含むスプレッドシートに対し、自動で列の追加・削除やデータの挿入、条件付き書式を適用するGASコードを作成する機会がありました。この内容を記録し、将来同じような課題に直面した際に参考にできるようまとめておきます。

 2. 対象読者Google Apps Scriptやスプレッドシートの操作に興味がある方
Excelやスプレッドシートを効率的に使いこなしたい方
WebマーケティングやSEO業務でデータ整理の効率化を目指している方

 3. 記事を読むメリットこの記事を読むことで、GASを使用してスプレッドシートの特定条件に基づいた編集を自動化する方法が学べます。これにより、繰り返し行う面倒な作業を効率化でき、時間の節約にもつながります。また、Zennフォーマットを活用したGASの実践的な利用方法が理解できるため、応用力も身に付きます。

 4. 結論Google Apps Scriptを使ってスプレッドシートを自動的に編集することで、業務効率を大幅に向上させることができます。今回のコードはAhrefsの「オーガニックキーワード」のcsvファイルには、必ずorganicというワードが入るので、「organic」を含むシートに対して多岐にわたる編集処理をまとめて実行できるため、同様のデータ管理において非常に役立つでしょう。

 5. 本文
 コード概要今回使用したGASコードでは、以下の処理を順番に行っています：
A列の隣に新しい列を追加

insertColumnAfter()を使い、A列の右側にB列を追加します。
B列にHYPERLINK関数を挿入

setFormula()メソッドを使い、B2以降のセルに =HYPERLINK("https://www.google.com/search?q="&A2,"SERPs") 関数を挿入し、A列の内容をもとにGoogle検索へのリンクを作成します。
「header」シートから1行目をコピー

getRange()とsetValues()メソッドを使い、headerシートの1行目をコピーし、「organic」を含むシートの1行目に貼り付けます。
不要な列を削除して左詰め

deleteColumn()メソッドで特定の列（C, E, F, G, K, N, O, P, R, S, T, U, V, W, X, Y）を削除し、データを左に詰めます。
列の移動

moveColumns()メソッドを使用し、G列をD列へ、H列をF列へ移動し、さらに右詰めします。
セルの背景色とフォントの設定

setBackground()とsetFontColor()メソッドで1行目の背景色を「#093058」、文字色を白、太文字に変更し、C列以外のセルの背景色を「#FFF0C5」に設定します。
条件付き書式設定

getValues()でF列のデータを取得し、セルの値に基づいて背景色を変更します。0より大きい場合は「#AFDEC6」、'-'が含まれる場合は「#F3C1BD」に設定します。

 コード以下が、GASで実装したコードの全体です。
function onOpen() {
  SpreadsheetApp.getUi().createMenu('カスタムメニュー')
    .addItem('ahrefs整列', 'arrangeAhrefs')
    .addToUi();
}

function arrangeAhrefs() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = spreadsheet.getSheets();
  const headerSheet = spreadsheet.getSheetByName("header");
  let targetSheet;

  sheets.forEach(sheet => {
    if (sheet.getName().includes("organic")) {
      targetSheet = sheet;
    }
  });

  if (!targetSheet) {
    SpreadsheetApp.getUi().alert("「organic」という名前を含むシートが見つかりません。");
    return;
  }

  targetSheet.insertColumnAfter(1);

  const lastRow = targetSheet.getLastRow();
  for (let i = 2; i <= lastRow; i++) {
    targetSheet.getRange(i, 2).setFormula(`=HYPERLINK("https://www.google.com/search?q="&A${i},"SERPs")`);
  }

  if (headerSheet) {
    const headerValues = headerSheet.getRange(1, 1, 1, headerSheet.getLastColumn()).getValues();
    targetSheet.getRange(1, 1, 1, headerValues[0].length).setValues(headerValues);
  }

  const columnsToDelete = [3, 5, 6, 7, 11, 14, 15, 16, 18, 19, 20, 21, 22, 23, 24, 25];
  columnsToDelete.reverse().forEach(col => {
    targetSheet.deleteColumn(col);
  });

  targetSheet.moveColumns(targetSheet.getRange("G:G"), 4);
  targetSheet.moveColumns(targetSheet.getRange("H:H"), 6);

  const headerRow = targetSheet.getRange("1:1");
  headerRow.setBackground("#093058").setFontColor("#FFFFFF").setFontWeight("bold");

  const dataRange = targetSheet.getRange(2, 3, lastRow - 1, 1);
  dataRange.setBackground("#FFF0C5");

  const fColumnRange = targetSheet.getRange(2, 6, lastRow - 1, 1);
  const fValues = fColumnRange.getValues();

  fValues.forEach((row, rowIndex) => {
    const cell = fColumnRange.getCell(rowIndex + 1, 1);
    const value = row[0];
    if (value > 0) {
      cell.setBackground("#AFDEC6");
    } else if (String(value).includes('-')) {
      cell.setBackground("#F3C1BD");
    }
  });
}

 6. まとめ今回は、Google Apps Scriptを使ったスプレッドシートの自動整形方法を紹介しました。特定の条件に基づいて複数の操作を一括で実行できるため、日々のデータ整理が大幅に効率化されます。今回の手法が役立ち、皆さんの業務効率が向上することを願っています。

1. はじめに

2. 対象読者

3. 記事を読むメリット

4. 結論

5. 本文

コード概要

コード

6. まとめ

Discussion