import pandas as pd def enblcid_split(df0): df1 = ( df0["eNB-LCID"] .str.split("-", expand=True) .rename(columns={0: "eNB", 1: "LCID"}) ) df2 = df0.join(df1) df2["LCID"] = df2["LCID"].str.split(",") df3 = df2.explode("LCID").astype({"eNB": int, "LCID": int}) df4 = df3.sort_values("eNB").reset_index(drop=True) return df4 # スプレッドシートのURL csv_url = "https://docs.google.com/spreadsheets/d/e/2PACX-1vQBgpr4RVBp6tZlIf9Y60dRFyvr2Na0036OUAJmxVe6RBNKaEl2c57hXXlBuasZpO7ptQf7UAIfXK3Y/pub?gid=498082614&single=true&output=csv" df1 = pd.read_csv(csv_url).dropna(how="all") df_my = enblcid_split(df1.dropna(subset=["eNB-LCID"])) df_my[df_my.duplicated(subset=["eNB", "LCID"], keep=False)] df_my.drop_duplicates(subset=["eNB", "LCID"], keep=False, inplace=True) df1 = df_my.pivot(index="eNB", columns="LCID", values="場所").sort_index() df2 = df1.reindex(range(df1.index.min(), df1.index.max()+1)) df2.to_csv("ehime.tsv", sep="\t")
上で作成のデータをコピペでスプレッドシートに貼り付け
下で結合と色付け
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("eNB-LCID"); const data = sheet.getDataRange().getValues(); function cellMarge(y, x, cnt, n) { if (cnt > 1) { if (cnt > 2 || x == 12) { range = sheet.getRange(y + 1, x + 2 - cnt, 1, n); // 結合 range.merge(); // 色付け if (x % 3 == 0) { // 1,4,7,10の時は黄色 range.setBackground("yellow"); } else { // それ以外は赤 range.setBackground("red"); } // 中央揃え range.setHorizontalAlignment("center"); cnt = 0; } } return cnt; } function myFunction() { // 最終行 let z = sheet.getLastRow(); for (let y = 1; y < z; y++) { let row = data[y]; let tmp = ""; let cnt = 0; for (let x = 1; x < 13; x++) { cnt++; if (row[x] == tmp) { // 前回と同じ cnt = cellMarge(y, x, cnt, cnt); } else { // 前回と違う cnt = cellMarge(y, x, cnt, cnt - 1); cnt = 1; } // 空白はカウント0 if (row[x] == "") cnt = 0; tmp = row[x]; } } }