モデル 1 で考えました。
q370876_onEdit
を編集時トリガーに指定するか、onEdit
に変更してください。
starter
は検索用シートにいくつか入力がある状態で、まとめて担当のシートを作る目的です。
checker
は蓄積シートをさわってしまったりして、データが連動していない状態を対応する目的です(特に、複数セルをコピペして担当が変更されたとき、前の担当者の情報が消し切れないので)。
質問と違って、担当を変更したときに、前の担当の蓄積シートでは背景を灰色で塗り潰しています。これは F 列から右には手入力がなされるとしており、その部分を暗黙に消すのは、担当者によっては好ましく感じないと考えたからです。
かといって、A-E 列のみを消してしまうと、F 列以降との整合性がとれなくなるので、目的に合いません(この点から私は query では対応できないと考えました)。
このため前の担当のシートでは、空白にするか、背景色をつけるか、といった対応が妥当に思いました。
ただ、蓄積シートの最終行が担当変更した場合に、空白だとその行を詰めることになると思うんですが、他と動作が異なることが気になるのと(実際には空白にした場合、つめられるかどうかは F 列以降に記入があるかにも影響を受けると想定します)、”前の担当”という情報は業務だと求められがちなので、背景色にしときました。
現在のコードだと、複数セルのコピペをしたときに、うまく動きません。
特に google apps script の仕様から複数セルの編集では編集前の状態が分からなくなるので、複数セルをコピペして担当が変更になる場合については原理的に対応できないです。
まとめてコピペして担当を変える、というのは、例えば担当者になる人の増員欠員の場合に起こりがちなので、使い勝手を考えるともっと具体的な業務分析が必要になりそうです。
javascript
1 const inputSheetName = ` 検索用シート ` ;
2 const requiredIndexes = ` AB ` . toUpperCase ( ) . split ( ` ` ) . map ( e => e . charCodeAt ( 0 ) - ` A ` . charCodeAt ( 0 ) ) ; ;
3 const inChargeSheetName = ( name ) => ` ${ name } 用の蓄積シート ` ;
4 const invalidColour = ` #444444 ` ;
5
6 const q370876_onEdit = ( e ) => {
7 const range = e . range ;
8 const sheet = range . getSheet ( ) ;
9 if ( sheet . getSheetName ( ) !== inputSheetName ) return ;
10 const r = range . getRow ( ) ;
11 if ( r === 1 ) return ;
12 const c = range . getColumn ( ) ;
13 if ( c > 4 ) return ;
14 let editingRow = sheet . getRange ( r , 1 , 1 , 5 ) . getValues ( ) [ 0 ] ;
15 if ( c === 1 ) {
16 const today = q370876_cascadeInputDate ( sheet , r ) ;
17 editingRow [ 4 ] = today ;
18 }
19 if ( ! q370876_isRequiredFulfilled ( editingRow ) ) return ;
20 q370876_upsertInChargeSheet ( editingRow ) ;
21 const previous = e . oldValue ;
22 if ( c !== 1 || previous === undefined ) return ;
23 q370876_cascadeDeleteInChargeSheet ( [ previous , editingRow [ 1 ] ] ) ;
24 }
25 // 蓄積シートがないときの初期設定用
26 const q370876_starter = ( ) => {
27 const values = SpreadsheetApp . getActive ( ) . getSheetByName ( inputSheetName ) ?. getDataRange ( ) . getValues ( ) . slice ( 1 ) ;
28 if ( ! values ) return ;
29 const personInCharges = [ ... new Set ( values . map ( ( [ e ] ) => e ) ) . values ( ) ] ;
30 personInCharges . forEach ( name => {
31 const sheet = q370876_getInChargeSheet ( name , true ) ;
32 const inCharge = values . filter ( ( [ e ] ) => e === name ) ;
33 sheet . getRange ( 2 , 1 , inCharge . length , inCharge [ 0 ] . length ) . setValues ( inCharge ) ;
34 } )
35 }
36 // 蓄積シートの整合性をチェックするとき用
37 const q370876_checker = ( ) => {
38 const inChargeSheets = SpreadsheetApp . getActive ( ) . getSheets ( ) ;
39 const data = new Map ( SpreadsheetApp . getActive ( ) . getSheetByName ( inputSheetName ) . getDataRange ( ) . getValues ( ) . map ( e => [ ` ${ e [ 0 ] } \t ${ e [ 1 ] } ` , e ] ) ) ;
40 inChargeSheets . forEach ( s => {
41 if ( ! s . getSheetName ( ) . endsWith ( inChargeSheetName ( ` ` ) ) ) return ;
42 const r = s . getLastRow ( ) ;
43 const range = s . getRange ( 1 , 1 , r , 5 ) ;
44 range . setBackground ( null ) ;
45 const values = range . getValues ( ) ;
46 values . forEach ( ( e , i ) => {
47 if ( i === 0 ) return ;
48 if ( ! data . has ( ` ${ e [ 0 ] } \t ${ e [ 1 ] } ` ) ) { s . getRange ( i + 1 , 1 , 1 , 5 ) . setBackground ( invalidColour ) ; }
49 } ) ;
50 const renewed = values . map ( e => data . has ( ` ${ e [ 0 ] } \t ${ e [ 1 ] } ` ) ? data . get ( ` ${ e [ 0 ] } \t ${ e [ 1 ] } ` ) : e ) ;
51 range . setValues ( renewed ) ;
52 } ) ;
53 }
54
55 const q370876_cascadeInputDate = ( sheet , rowNumber ) => {
56 const dateColumn = 5 ;
57 const now = new Date ( ) ;
58 const today = new Date ( now . getFullYear ( ) , now . getMonth ( ) , now . getDate ( ) , 0 , 0 , 0 , 0 ) ;
59 sheet . getRange ( rowNumber , dateColumn ) . setValue ( today ) ;
60 return today ;
61 }
62 const q370876_cascadeDeleteInChargeSheet = ( row ) => {
63 const sheet = q370876_getInChargeSheet ( row [ 0 ] , false ) ;
64 const indexes = sheet . getDataRange ( ) . getValues ( ) . reduce ( ( a , c , i ) => q370876_isMatchRows ( c , row ) ? [ ... a , i ] : a , [ ] ) . map ( e => e + 1 ) ;
65 if ( indexes . length < 1 ) {
66 console . log ( "unexpected code path" ) ;
67 return ;
68 }
69 indexes . forEach ( r => sheet . getRange ( r , 1 , 1 , 5 ) . setBackground ( invalidColour ) ) ;
70 }
71 const q370876_upsertInChargeSheet = ( row ) => {
72 const sheet = q370876_getInChargeSheet ( row [ 0 ] , false ) ;
73 const indexes = sheet . getDataRange ( ) . getValues ( ) . reduce ( ( a , c , i ) => q370876_isMatchRows ( c , row ) ? [ ... a , i ] : a , [ ] ) . map ( e => e + 1 ) ;
74 if ( indexes . length < 1 ) {
75 sheet . appendRow ( row ) ;
76 return ;
77 }
78 indexes . forEach ( r => {
79 const range = sheet . getRange ( r , 1 , 1 , row . length ) ;
80 range . setValues ( [ row ] ) ;
81 range . setBackground ( null ) ;
82 } ) ;
83 }
84 const q370876_isMatchRows = ( a , b ) => requiredIndexes . every ( i => a [ i ] === b [ i ] ) ;
85 const q370876_getInChargeSheet = ( name , reset = true ) => {
86 const sheetName = inChargeSheetName ( name )
87 const existingSheet = SpreadsheetApp . getActive ( ) . getSheetByName ( sheetName ) ;
88 if ( ( ! reset ) && existingSheet ) return existingSheet ;
89 const renewSheet = existingSheet ?. clear ( ) ?? SpreadsheetApp . getActive ( ) . insertSheet ( sheetName ) ;
90 renewSheet . appendRow ( ` 担当者名:\t担当A\t\t\t▼顧客登録日 ` . split ( ` \t ` ) ) ;
91 return renewSheet ;
92 }
93 const q370876_isRequiredFulfilled = ( value ) => requiredIndexes . every ( e => value [ e ] !== ` ` ) ;
94 const initializer = ( ) => {
95 const dat = ` 担当者\t顧客名\tフリガナ\t性別\t顧客登録日
96 担当A\t田中\tタナカ\t男\t09/21
97 担当B\t鈴木\tスズキ\t女\t10/01
98 担当A\t佐藤\tサトウ\t女\t10/04
99 担当B\t山田\tヤマダ\t男\t11/12 ` ;
100 const inputSheet = SpreadsheetApp . getActive ( ) . getSheetByName ( inputSheetName ) ?. clear ( ) ?? SpreadsheetApp . getActive ( ) . insertSheet ( inputSheetName ) ;
101 const value = dat . split ( ` \n ` ) . map ( e => e . split ( ` \t ` ) ) ;
102 inputSheet . getRange ( 1 , 1 , value . length , value [ 0 ] . length ) . setValues ( value ) ;
103 SpreadsheetApp . getActive ( ) . getSheets ( ) . forEach ( s => { if ( s . getSheetName ( ) !== inputSheetName ) { SpreadsheetApp . getActive ( ) . deleteSheet ( s ) ; } } ) ;
104 }