Skip to content

Instantly share code, notes, and snippets.

@halllllll
Last active November 4, 2021 10:15
Show Gist options
  • Save halllllll/ff3946577d09dac9d9a5cc72bf4f58eb to your computer and use it in GitHub Desktop.
Save halllllll/ff3946577d09dac9d9a5cc72bf4f58eb to your computer and use it in GitHub Desktop.
Google Apps Script - generate dummy account on sheet
const sheet = SpreadsheetApp.getActiveSheet();
/**
* カスタムメニュー作成
*/
function onOpen(e) {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('GIG');
menu.addItem("ダミーアカウント追加", 'genDummyMenu_');
menu.addToUi();
}
/**
* ダミーデータを生成するよ
*/
function genDummyMenu_(){
const html = HtmlService.createHtmlOutputFromFile('gen_dummy_account');
SpreadsheetApp.getUi().showModelessDialog(html, "ダミーアカウント追加");
}
/**
* Sheetの末尾にダミーアカウントを追加する
* name_column, id_columnは決め打ちにしているのでシートによって変えようね
*/
function setDummyAccount(count){
const num = parseInt(count);
// シート名で分岐
const sheet_name = sheet.getSheetName();
console.log(`sheet name : ${sheet_name}`);
let [name_column, id_column] = [0, 0];
if(sheet_name === "student"){
name_column = 0;
id_column = 4;
}else if(sheet_name === "teacher"){
name_column = 0;
id_column = 1;
}else{
return Error("シート名がおかしい?");
}
// 最後尾に追加していくだけ
const cur_range = sheet.getDataRange();
const height = cur_range.getHeight();
const width = cur_range.getWidth();
const target_range = sheet.getRange(height+1, 1, num, width);
const target_values = [];
for(let h=0; h<num; h++){
const tmp_row = [];
const [name, acc_id] = genDummyNameAndId_();
for(let w=0; w<width; w++){
if(w===name_column){
// 名前挿入
tmp_row.push(name);
}else if(w===id_column){
// ID挿入
tmp_row.push(acc_id);
}else{
tmp_row.push("");
}
}
target_values.push(tmp_row);
}
console.log(`range H: ${target_range.getHeight()} W: ${target_range.getWidth()}`);
console.log(`value H: ${target_values.length} W: ${target_values[0].length}`);
target_range.setValues(target_values);
}
function genDummyNameAndId_(){
// 名前を適当に あいうえお 姓2~5 名2~6くらい?
const kana = "あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもやゐゆゑよわをんがぎぐげござじずぜぞだぢづでどばびぶぶべぼぱぴぷぺぽ";
const randRange = (min, max) => Math.floor(Math.random() * (max - min + 1) + min);
const genName = (n, m) => Array.from(Array(randRange(n, m))).map(()=>kana[Math.floor(Math.random()*kana.length)]).join("");
const [sei, mei] = [genName(2, 5), genName(2, 6)];
const romanMap = new Map([
['あ' , 'a'],
['い' , 'i'],
['う' , 'u'],
['え' , 'e'],
['お' , 'o'],
['か' , 'k'],
['き' , 'k'],
['く' , 'k'],
['け' , 'k'],
['こ' , 'k'],
['さ' , 's'],
['し' , 's'],
['す' , 's'],
['せ' , 's'],
['そ' , 's'],
['た' , 't'],
['ち' , 'c'],
['つ' , 't'],
['て' , 't'],
['と' , 't'],
['な' , 'n'],
['に' , 'n'],
['ぬ' , 'n'],
['ね' , 'n'],
['の' , 'n'],
['は' , 'h'],
['ひ' , 'h'],
['ふ' , 'f'],
['へ' , 'h'],
['ほ' , 'h'],
['ま' , 'm'],
['み' , 'm'],
['む' , 'm'],
['め' , 'm'],
['も' , 'm'],
['や' , 'y'],
['ゆ' , 'y'],
['よ' , 'y'],
['ら' , 'r'],
['り' , 'r'],
['る' , 'r'],
['れ' , 'r'],
['ろ' , 'r'],
['わ' , 'w'],
['ゐ' , 'w'],
['ゑ' , 'w'],
['を' , 'o'],
['ん' , 'n'],
['が' , 'g'],
['ぎ' , 'g'],
['ぐ' , 'g'],
['げ' , 'g'],
['ご' , 'g'],
['ざ' , 'z'],
['じ' , 'j'],
['ず' , 'z'],
['ぜ' , 'z'],
['ぞ' , 'z'],
['だ' , 'd'],
['ぢ' , 'd'],
['づ' , 'd'],
['で' , 'd'],
['ど' , 'd'],
['ば' , 'b'],
['び' , 'b'],
['ぶ' , 'b'],
['べ' , 'b'],
['ぼ' , 'b'],
['ぱ' , 'p'],
['ぴ' , 'p'],
['ぷ' , 'p'],
['ぺ' , 'p'],
['ぽ' , 'p'],
]);
const initial = romanMap.get(mei[0]);
console.log(`名 ${mei} 頭文字 ${mei[0]} initial ${romanMap.get(mei[0])}`);
return [`${sei} ${mei}`, `${initial}${randRange(16, 21)}-${randRange(1, 9999).toString().padStart(4, 0)}@xxxxxxxxx.xxxxx`];
}
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>/*なんかメニューに追加したGUIだとincludeができないっぽい?のでそれぞれhtmlに直書き*/
@charset "UTF-8";
body{
}
ul {
list-style: none;
padding: 0px;
}
h1{
text-align: center;
padding: 20px;
}
form{
max-width: 290px;
margin: 0 auto;
}
input{
width: 260px;
border-radius: 10px;
padding: 10px;
margin-bottom: 5px;
}
select{
width: 100px;
border-radius: 10px;
padding: 10px;
margin-bottom: 5px;
}
input#submit_button{
width: 40%;
margin: 10px;
text-align: center;
}
</style>
<script>
</script>
</head>
<body>
<form id="form" enctype="multipart/form-data">
<div>
<select class="element select small" id="count" name="count">
<option value="5" selected="selected">5</option>
<option value="10">10</option>
<option value="20">20</option>
<option value="50">50</option>
</select>
</div>
<input id="submitForm" class="button_text" type="submit" name="submit" value="ダミーアカウント作成するよ~")>
</form>
<script type="text/javascript">
function logSubmit(event) {
const formData = new FormData(form);
// なぜかformdataだとSSに送れない?のでせっかくformdataにしたけど手で抽出する
const count = formData.get("count");
google.script.run.withSuccessHandler(()=>{
google.script.host.close();
alert('反映しました!!!😤');
}).withFailureHandler((e)=>{
alert(`失敗しました...🥺🥺🥺 ${e}`);
}).setDummyAccount(count);
event.preventDefault();
}
const form = document.getElementById('form');
form.addEventListener('submit', logSubmit);
</script>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment