前提・実現したいこと
Google Spreadsheetにてデータ整形を行いたいです
以下に添付したSample(タブ名: 'From')のように、「商品の販売数」と「前月との増減割合」を月毎に35ヶ月分(2019年1月-2021年11月)あり、それが100商品以上分連続して右側の列に列挙しています(35ヶ月分×商品数)
そのデータをSample(タブ名: 'To')のように2019年の12月分、2020年の12ヶ月分、2021年の11ヶ月分のデータを年ごとに縦に並べ替えたいです
https://docs.google.com/spreadsheets/d/1kOCVzEh8D3aNKSy7CUNh0rmcpK4mT00uPSpRCodPWNI/edit?usp=sharing
該当のソースコード
javascript
1function valueCopy() { 2 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 3 var sheet = spreadsheet.getSheetByName("From"); 4 5 //getRangesでFromタブの該当範囲を全て選択する 6 //getValuesで最も上の行が2019%であれば指定行数×12列、2020%の場合も同じく指定行数×12列、2021%のときは指定行数×11列のデータを得る 7 //getLastRowで得た行×指定列にsetValuesする 8 //for文で'From'タブの最後のデータブロック(最後の商品の2021年の11月分)を取得して貼り付けが終わった後にbreak 9}
試したこと
javascriptについて全くの素人ですが、調べた限り以下のような考え方をするんだろうなと思っています
getRangesでFromタブの該当範囲を全て選択し、getValuesで最も上の行が2019%であれば指定行数×12列、2020%の場合も同じく指定行数×12列、2021%のときは指定行数×11列をgetValuesし、getLastRowで得た行×指定列にsetValuesする
for文で'From'タブの最後のデータブロック(最後の商品の2021年の11月分)を取得して貼り付けが終わった後にbreak
ただ初めてのjavascriptで、実際にrunできるものを自分で書くことができないので、お力をお貸しいただきたく思います
補足情報(FW/ツールのバージョンなど)
Google検索をしても解決できなかったのと、teratail上に似た質問が見当たらなかったので質問させていただきます
宜しくお願いいたします
気になる質問をクリップする
クリップした質問は、後からいつでもMYページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
回答2件
0
データ構造から "YYYY-MM-DD" という標識がある、ことは信頼しても良さそうに思えた。
javascript
1const q373076 = () => { 2 const srcSheetName = `From`; 3 const dstSheetName = `To`; 4 const dstOffset = [2,2]; 5 const suppress = false; 6 const srcSheet = SpreadsheetApp.getActive().getSheetByName(srcSheetName); 7 const dstSheet = SpreadsheetApp.getActive().getSheetByName(dstSheetName); 8 const srcValues = srcSheet.getDataRange().getValues(); 9 const areas = srcSheet.createTextFinder(`YYYY-MM-DD`).findAll().filter(e=> e !== null).map(e=>new region(e.getRow(),e.getColumn())); 10 const rs = [...new Set(areas.map(e=>e.getTop())).values()]; 11 const areaMap = new Map(rs.map(t=>[t,areas.filter(e=>e.getTop()===t)])); 12 Array.from({length:srcValues[0].length + 1},(_,i)=>i).forEach(e=>{ 13 areas.forEach(a=>{if(a.isExtensibleAt(e,areaMap)){a.extend();}}); 14 }); 15 const data = areas.reduce((a,c)=>a.concat(pickByRegion(srcValues,c)),[]); 16 const d = new dataHandler(data,suppress).buildData(); 17 dstSheet.getRange(dstOffset[0],dstOffset[1],d.length,d[0].length).setValues(d); 18} 19const compare = (a,b) => (a > b) ? 1 : ((a < b) ? -1 : 0); 20const pickByRegion = (values, region) => { 21 let result = []; 22 for(const cols of region.getRangeListForArrayIndex()) { 23 const row = cols.map(c=>values[c[0]][c[1]]); 24 if(row[0] === "" || row[1] === "") continue; 25 result.push(row); 26 } 27 return result; 28} 29 30class region { 31 constructor(r,c) { 32 this.t = r; 33 this.l = c; 34 this.r = c; 35 } 36 getTop() { 37 return this.t; 38 } 39 getLeft() { 40 return this.l; 41 } 42 getRight() { 43 return this.r; 44 } 45 getRangeListForArrayIndex() { 46 let result = []; 47 for(let i = this.getLeft(); i < this.getRight(); i++) { 48 result.push([[this.getTop()-1,i],[this.getTop(),i],[this.getTop()+1,i],[this.getTop()+2,i]]); 49 } 50 return result; 51 } 52 isExtensibleAt(c,map) { 53 if(c !== this.getRight() + 1) return false; 54 for(let i = Math.max(this.getTop()-3,0) ; i < this.getTop() + 3; i++ ) { 55 if(!map.has(i)) { continue; } 56 if(map.get(i).some(e=>e.getLeft() === c)) return false; 57 } 58 return true; 59 } 60 extend() { 61 this.r += 1; 62 } 63} 64 65class dataHandler { 66 constructor(data,suppress = false) { 67 this.suppress = suppress; 68 this.sellingInfos = data.map(e=> new sellingInfo(...e)); 69 this.years = [...new Set(this.sellingInfos.map(e=>e.getSoldYear())).values()].sort(compare); 70 this.months = [...new Set(this.sellingInfos.map(e=>e.getSoldMonth())).values()].sort(compare); 71 this.prodNames = [...new Set(this.sellingInfos.map(e=>e.getProdName())).values()].sort(compare); 72 this.data = new Map(this.prodNames.map(e=>[e, new Map(this.years.map(y=> [y,new Map(this.months.map(m=> [m, this.sellingInfos.filter(f=>f.getProdName() === e && f.getSoldYear() === y && f.getSoldMonth() === m)[0]]))]))])); 73 } 74 buildData() { 75 let result = []; 76 for(const prod of this.prodNames) { 77 const vacant = Array.from({length:this.months.length},_=>``); 78 for(const year of this.years) { 79 const isProdSoldInThisYear = [...this.data.get(prod).get(year).values()].every(e=> e !== undefined); 80 if(this.suppress && !isProdSoldInThisYear) { continue; } 81 result.push(this.months.map(m=> monthInfo.getFormatted(year,m,1))); 82 result.push(Array.from({length:vacant.length},_=>prod)); 83 if(!isProdSoldInThisYear) { 84 result.push(vacant.slice()); 85 result.push(vacant.slice()); 86 continue; 87 } 88 result.push(this.months.map(m=> this.data.get(prod).get(year).get(m)?.getAmount() ?? '')); 89 result.push(this.months.map(m=> this.data.get(prod).get(year).get(m)?.getRate() ?? '')); 90 } 91 } 92 return result; 93 } 94} 95class monthInfo { 96 constructor(ymd) { 97 const [y, m] = ymd.split(`-`); 98 this.year = Number(y); 99 this.month = Number(m); 100 this.value = ymd; 101 } 102 getYear() { 103 return this.year; 104 } 105 getMonth() { 106 return this.month; 107 } 108 static getFormatted(y,m,d) { 109 return `${y.toString().padStart(4,`0`)}-${m.toString().padStart(2,`0`)}-${d.toString().padStart(2,`0`)}`; 110 } 111} 112class sellingInfo { 113 constructor(ymd,prod,amount,rate) { 114 this.ymd = new monthInfo(Utilities.formatDate(ymd,"Asia/Tokyo", "yyyy-MM-dd")); 115 this.id = prod; 116 this.amount = amount; 117 this.rate = rate; 118 } 119 getProdName() { 120 return this.id; 121 } 122 getSoldYear() { 123 return this.ymd.getYear(); 124 } 125 getSoldMonth() { 126 return this.ymd.getMonth(); 127 } 128 getAmount() { 129 return this.amount; 130 } 131 getRate() { 132 return this.rate; 133 } 134}
投稿2021/12/29 07:40
総合スコア12705
0
ベストアンサー
手なりで書いたら下記のような感じでしょうか。
mapやらreduceやら駆使すればもっとすっきり書けるかもしれません。
※下記は年月が連続している前提です。
js
1function myFunction1() { 2 const ssFrom = SpreadsheetApp.getActive().getSheetByName("From"); 3 const ssTo = SpreadsheetApp.getActive().getSheetByName("To"); 4 // データの読み取り開始列 5 const startColumn = 3; // C列 6 const lastColumn = ssFrom.getLastColumn(); 7 // データを2次元配列に読み込む。(となりの列との比較のため1列分余計に読み込んでいる) 8 const values = ssFrom.getRange(2, startColumn, 4, lastColumn - startColumn + 2).getDisplayValues(); 9 // 書き込む行ブロックのカウンタ 10 let row = 0; 11 12 // 1商品1年分のデータを書き込むためのバッファ。(4行) 13 let buff = Array.from(new Array(4), _ => new Array()); 14 15 // 1列ずつ読み取る 16 for (let col = 0; col < values[0].length-1; col++) { 17 let date = values[0][col]; // 年月 18 let year = values[0][col].slice(0, 4); // 年 19 let name = values[1][col]; // 商品名 20 let amount = values[2][col]; // 数量 21 let ratio = values[3][col]; // 前月比 22 23 // バッファに格納する。 24 buff[0].push(date); 25 buff[1].push(name); 26 buff[2].push(amount); 27 buff[3].push(ratio); 28 29 30 let nextName = values[1][col+1]; // 右隣の商品名 31 let nextYear = values[0][col+1].slice(0, 4); //右隣の年 32 // 現在の列の商品名または年が、右隣の列と異なる場合、Toシートにバッファを書き込んで改行。 33 if (col !== 0 &&(nextName !== name || nextYear !== year)) { 34 ssTo.getRange(3 + row * 4, 2, buff.length, buff[0].length).setValues(buff); 35 // バッファを初期化 36 buff = Array.from(new Array(4), _ => new Array()); 37 // 行ブロックをインクリメント(改行) 38 row++; 39 } 40 } 41}
途中でデータのない月が存在する場合に対応したコードは下記のような感じでしょうか。
(冗長な部分があるかもしれない点については同上)
js
1function myFunction2() { 2 const ssFrom = SpreadsheetApp.getActive().getSheetByName("From"); 3 const ssTo = SpreadsheetApp.getActive().getSheetByName("To"); 4 // データの読み取り開始列 5 const startColumn = 3; // C列 6 const lastColumn = ssFrom.getLastColumn(); 7 // データを2次元配列に読み込む。 8 const values = ssFrom.getRange(2, startColumn, 4, lastColumn - startColumn + 1).getDisplayValues(); 9 // データを格納する連想配列 10 const items = {}; 11 12 // 月の列名 13 const monthArray = [...Array(12)].map((_, i) => ('00' + (i + 1)).slice(-2)) 14 15 // 商品名->年->月をキーとする連想配列を作成。 16 for (let col = 0; col < values[0].length; col++) { 17 let date = values[0][col]; // 年月 18 let year = date.slice(0, 4); // 年 19 let month = date.slice(5, 7); // 月 20 let name = values[1][col]; // 商品名 21 let amount = values[2][col]; // 数量 22 let ratio = values[3][col]; // 前月比 23 24 if (!items.hasOwnProperty(name)) 25 items[name] = {}; 26 27 if (!items[name].hasOwnProperty(year)) 28 items[name][year] = {}; 29 30 items[name][year][month] = { date, name, amount, ratio }; 31 } 32 33 // 書き込む行ブロックのカウンタ 34 let row = 0; 35 // 商品名->年をキーとしてデータを取り出す。 36 Object.keys(items).forEach((name) => { 37 Object.keys(items[name]).forEach((year) => { 38 // 1商品1年分のデータを書き込むためのバッファ。(4行×12列) 39 const buff = Array.from(new Array(4), _ => new Array(12).fill('')); 40 // 月の列に合致するデータを取り出してバッファに格納する。 41 monthArray.forEach((month, monthIdx) => { 42 buff[0][monthIdx] = items[name][year][month]?.date || ''; 43 buff[1][monthIdx] = items[name][year][month]?.name || ''; 44 buff[2][monthIdx] = items[name][year][month]?.amount || ''; 45 buff[3][monthIdx] = items[name][year][month]?.ratio || ''; 46 }); 47 // バッファをToシートに書き込む。 48 ssTo.getRange(3 + row * 4, 2, buff.length, buff[0].length).setValues(buff); 49 // 行ブロックをインクリメント(改行) 50 row++; 51 }); 52 }); 53}
投稿2021/12/11 07:19
退会済みユーザー
総合スコア0
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
あなたの回答
tips
太字
斜体
打ち消し線
見出し
引用テキストの挿入
コードの挿入
リンクの挿入
リストの挿入
番号リストの挿入
表の挿入
水平線の挿入
プレビュー
質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。