ブラウザだけで学べる Googleスプレッドシートプログラミング入門 本書掲載リスト リスト2-1 /** @OnlyCurrentDoc */ function macro1() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('A1:A10').activate(); spreadsheet.insertSheet(2); spreadsheet.getCurrentCell().setValue('1'); spreadsheet.getRange('A2').activate(); spreadsheet.getCurrentCell().setValue('2'); spreadsheet.getRange('A1:A2').activate(); spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A1:A10'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES); spreadsheet.getRange('A1:A10').activate(); }; function macro2() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getCurrentCell().setValue('1'); spreadsheet.getCurrentCell().offset(1, 0).activate(); spreadsheet.getCurrentCell().setValue('2'); spreadsheet.getCurrentCell().offset(-1, 0, 2, 1).activate(); var destinationRange = spreadsheet.getActiveRange().offset(0, 0, 10); spreadsheet.getActiveRange().autoFill(destinationRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES); spreadsheet.getCurrentCell().offset(0, 0, 10, 1).activate(); }; リスト2-2 function macro3() { } リスト2-3 function macro3() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getCurrentCell().setValue('No,1'); //☆ spreadsheet.getCurrentCell().offset(0, 1).activate(); spreadsheet.getCurrentCell().setValue('No,2'); //☆ spreadsheet.getCurrentCell().offset(0, -1, 1, 2).activate(); //☆ var destinationRange = spreadsheet.getActiveRange().offset(0, 0, 1, 10); //☆ spreadsheet.getActiveRange().autoFill(destinationRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES); spreadsheet.getCurrentCell().offset(0, 0, 1, 10).activate(); //☆ } リスト3-3-1 function hello() { const price = 12300; var tax = 0.08; var result = price * (1.0 + tax); console.log('価格:' + price); console.log('税込価格:' + result); tax = 0.1; result = price * (1.0 + tax); console.log('価格:' + price); console.log('税込価格:' + result); } リスト3-5-1 function hello() { const num = 12345; // ☆ if (num % 2 == 0) { console.log(num + 'は、偶数です。'); } else { console.log(num + 'は、奇数です。'); } } リスト3-6-1 function hello() { const month = 4; //☆月の値 var m = month; if (m == 12) { m = 0; } switch(Math.floor(m / 3)) { case 0: console.log(month + '月は、冬です。'); break; case 1: console.log(month + '月は、春です。'); break; case 2: console.log(month + '月は、夏です。'); break; case 3: console.log(month + '月は、秋です。'); break; default: console.log('よくわかりません。'); } } リスト3-7-1 function hello() { const max = 100; //☆ var total = 0; var count = 1; while(count <= max) { total += count; count++; } console.log(max + 'までの合計は、' + total); } リスト3-8-1 function hello() { const max = 100; //☆ var total = 0; for(var i = 1;i <= max;i++) { total += i; } console.log(max + 'までの合計は、' + total); } リスト3-9-1 function hello() { const data = [0, 198, 76, 54]; data[0] = data[1] + data[2] + data[3]; console.log('合計は、' + data[0]); } リスト3-9-2 function hello() { const data = [12, 34, 56, 78, 90]; var total = 0; for(var n in data) { total += data[n]; } console.log('合計は、' + total); console.log('平均は、' + (total / data.length)); } リスト3-10-1 function hello() { msg('Taro'); msg('Hanako'); } function msg(name) { console.log('Hello, ' + name + '!!'); } リスト3-10-2 function hello() { const taro = msg('Taro'); const hanako = msg('Hanako'); console.log(taro); // ☆ console.log(hanako); // ☆ } function msg(name) { return 'Hello, ' + name + '!!'; } リスト3-11-1 function hello() { const taro = msg('Taro'); const hanako = msg('Hanako'); } var msg = function(name) { console.log('Hello, ' + name + '!!'); } リスト3-12-1 function hello() { myobj.print(); } const myobj = { name: 'Taro', mail: 'taro@yamada', print: function() { console.log('<< NAME: ' + this.name + ', MAIL: ' + this.mail + ' >>'); } } リスト4-1-1 function macro3() { const spreadsheet = SpreadsheetApp.getActive(); const sheet = spreadsheet.getActiveSheet(); const cell = sheet.getActiveCell(); cell.setValue('★HERE!!★'); }; リスト4-2-1 function macro3() { const spreadsheet = SpreadsheetApp.getActive(); const sheet = spreadsheet.getActiveSheet(); var cell = sheet.getRange('A1'); cell.setValue('★HERE!!★'); cell = sheet.getRange(2, 2); cell.setValue('★HERE!!★'); }; リスト4-3-1 function macro3() { const spreadsheet = SpreadsheetApp.getActive(); const sheet = spreadsheet.getActiveSheet(); var cell = sheet.getRange(1, 1, 5, 2); cell.setValues([ ['東京',1230], ['大阪',980], ['名古屋',760], ['札幌',540], ['仙台', 320] ]); }; リスト4-4-1 function macro3() { const spreadsheet = SpreadsheetApp.getActive(); const sheet = spreadsheet.getActiveSheet(); var cell = sheet.getRange(1, 1, 5, 2); cell.setFontSize(12); cell = sheet.getRange(1, 1, 5, 1); cell.setFontWeight('bold'); }; リスト4-5-1 function macro3() { const spreadsheet = SpreadsheetApp.getActive(); const sheet = spreadsheet.getActiveSheet(); var cellA = sheet.getRange(1, 1, 5); cellA.setFontColor('white'); cellA.setBackground('blue'); }; リスト4-6-1 function macro3() { const spreadsheet = SpreadsheetApp.getActive(); const sheet = spreadsheet.getActiveSheet(); var cell = sheet.getRange(1, 1, 5, 2); cell.setBorder(true, true, true, true, null, null, 'black', SpreadsheetApp.BorderStyle.DOUBLE); var cellA = sheet.getRange(1, 1, 5, 1); cellA.setBorder(null, null, null, null, null, true, '#cccccc', SpreadsheetApp.BorderStyle.SOLID); var cellB = sheet.getRange(1, 2, 5, 1); cellB.setBorder(null, null, null, null, true, true, '#666666', SpreadsheetApp.BorderStyle.SOLID); }; リスト4-7-1 function macro3() { const spreadsheet = SpreadsheetApp.getActive(); const sheet = spreadsheet.getActiveSheet(); var cell = sheet.getRange(1, 2, 5, 2); cell.setNumberFormat("¥#,###.0"); }; リスト4-8-1 function macro3() { const spreadsheet = SpreadsheetApp.getActive(); const sheet = spreadsheet.getActiveSheet(); sheet.getRange('A7').setValue('total'); sheet.getRange('B7').setFormula('=SUM(B1;B5)'); }; リスト4-9-1 function macro3() { const spreadsheet = SpreadsheetApp.getActive(); const sheet = spreadsheet.getActiveSheet(); const cell = sheet.getRange(1, 1, 5, 2); var chart = sheet.newChart() .asBarChart() .addRange(cell) .setPosition(10, 1, 10, 10) .setOption('height', 300) .setOption('width', 400) .build(); sheet.insertChart(chart); }; リスト4-10-1 function macro3() { const spreadsheet = SpreadsheetApp.getActive(); const sheet = spreadsheet.getActiveSheet(); var chart = sheet.getCharts()[0]; chart = chart.modify() .setChartType(Charts.ChartType.PIE) .setOption('title', '売上グラフ') .build(); sheet.updateChart(chart); }; リスト5-2-1 function macro3() { const ui = SpreadsheetApp.getUi(); ui.alert('これがアラートです。'); }; リスト5-2-2 function macro3() { const ui = SpreadsheetApp.getUi(); const res = ui.alert('注意!', 'そのまま実行しますか?', ui.ButtonSet.OK_CANCEL); if (res == ui.Button.OK) { ui.alert('Thanks!!'); } }; リスト5-3-1 function macro3() { const ui = SpreadsheetApp.getUi(); const res = ui.prompt('お名前は?'); const msg = res.getResponseText(); ui.alert('こんにちは、' + msg + 'さん!'); }; リスト5-3-2 function macro3() { const ui = SpreadsheetApp.getUi(); const res = ui.prompt('入力', 'お名前は?', ui.ButtonSet.YES_NO_CANCEL); const btn = res.getSelectedButton(); const msg = res.getResponseText(); switch(btn) { case ui.Button.YES: ui.alert('こんにちは、' + msg + 'さん!'); break; case ui.Button.NO: ui.alert(msg + 'さんでは、ない?'); break; } }; リスト5-4-1 function macro3() { var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getActiveSheet(); const ui = SpreadsheetApp.getUi(); const res = ui.prompt('入力', '行数を入力:', ui.ButtonSet.OK_CANCEL); const btn = res.getSelectedButton(); const msg = res.getResponseText(); const n = msg * 1; if (btn == ui.Button.OK) { var cell = sheet.getActiveCell(); const r = cell.getRow(); const c = cell.getColumn(); const data = []; for(var i = 1;i <= n;i++) { data.push([i]); } var cell = sheet.getRange(r, c, n, 1); cell.activate(); cell.setValues(data); } }; リスト5-5-1 function macro3() { const ui = SpreadsheetApp.getUi(); ui.createMenu('My Menu') .addItem('Set Bg Color', 'setBgColor') .addItem('Set Color', 'setColor') .addSeparator() .addItem('Remove menu', 'removeMenu') .addToUi(); } リスト5-6-1 function setColor() { const ui = SpreadsheetApp.getUi(); const res = ui.prompt('入力', 'テキストのカラーを入力:', ui.ButtonSet.OK_CANCEL); const btn = res.getSelectedButton(); const msg = res.getResponseText(); if (btn == ui.Button.OK) { var cell = SpreadsheetApp.getActiveRange(); cell.setFontColor(msg); } } function setBgColor() { const ui = SpreadsheetApp.getUi(); const res = ui.prompt('入力', '背景のカラーを入力:', ui.ButtonSet.OK_CANCEL); const btn = res.getSelectedButton(); const msg = res.getResponseText(); if (btn == ui.Button.OK) { var cell = SpreadsheetApp.getActiveRange(); cell.setBackground(msg); } } リスト5-6-3 function removeMenu() { var spsheet = SpreadsheetApp.getActiveSpreadsheet(); spsheet.removeMenu('My Menu'); } リスト5-7-1 function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu('My Menu') .addItem('Set Bg Color', 'setBgColor') .addItem('Set Color', 'setColor') .addSeparator() .addItem('Remove menu', 'removeMenu') .addToUi(); } リスト5-8-1 function btnClick() { const ui = SpreadsheetApp.getUi(); ui.alert("ボタンをクリックしました!"); } リスト5-9-1 function データを追加() { const ui = SpreadsheetApp.getUi(); const spreadsheet = SpreadsheetApp.getActive(); const sheet = spreadsheet.getSheetByName('データ'); const lastrow = sheet.getLastRow() + 1; var res = ui.prompt("名前を入力:", ui.ButtonSet.OK_CANCEL); if (res.getSelectedButton() == ui.Button.CANCEL) { return; } const name = res.getResponseText(); res = ui.prompt("メールアドレスを入力:", ui.ButtonSet.OK_CANCEL); if (res.getSelectedButton() == ui.Button.CANCEL) { return; } const mail = res.getResponseText(); const vals = [[name, mail]]; sheet.getRange(lastrow, 1, 1, 2).setValues(vals); ui.alert('データを追加しました。'); } リスト5-10-1 function データを検索() { const ui = SpreadsheetApp.getUi(); const spreadsheet = SpreadsheetApp.getActive(); const sheet = spreadsheet.getSheetByName('データ'); var res = ui.prompt("名前を入力:", ui.ButtonSet.OK_CANCEL); if (res.getSelectedButton() == ui.Button.CANCEL) { return; } const find = res.getResponseText(); const data = sheet.getDataRange().getValues(); for(var i in data) { var item = data[i]; if (item[0] == find) { ui.alert(item[0] + ', ' + item[1]); } } } リスト6-11-1 function makePivot() { var sheet = SpreadsheetApp.getActiveSheet(); var src = sheet.getActiveRange(); var r = src.getLastRow(); var pivot = sheet.getRange(r + 2, 1).createPivotTable(src); pivot.addRowGroup(4); // 教科を行に指定 pivot.addColumnGroup(2); // 試験を列に指定 pivot.addPivotValue(5,SpreadsheetApp.PivotTableSummarizeFunction.SUM); }; リスト7-1-1 function SendMail() { const to = '…メールアドレス…'; // ☆ const title = 'テストで送る'; const body = 'これは、テストで送信するメールです。'; MailApp.sendEmail(to, title, body); }; リスト7-2-1 function SendDataByMail() { var r = SpreadsheetApp.getActiveRange(); var v = r.getValues(); var result = '※データを送信します。\n\n'; for(var i = 1;i < v.length;i++) { result += v[i].join(', ') + '\n'; } result += '\n以上です。'; const to = '…メールアドレス…'; // ☆ const title = 'データの送信'; MailApp.sendEmail(to, title, result); } リスト7-3-1 function SendDataByMail() { var r = SpreadsheetApp.getActiveRange(); var v = r.getValues(); var result = ''; result += ''; result += ''; for(var i = 1;i < v.length;i++) { result += ''; } result += '
' + v[0].join('') + '
' + v[i].join('') + '
' var content = '

Hello!

これはサンプルで用意したメッセージです。

'; content += result; const options = { to: '…メールアドレス…', // ☆ subject: 'HTMLメールを送る', htmlBody:content, }; // HTMLメールを送信 MailApp.sendEmail(options); } リスト7-4-1 function sendEmailWithChart(){ const sheet = SpreadsheetApp.getActiveSheet(); const charts = sheet.getCharts(); var body="

Chart!

"; const images={}; for (var i = 0;i < charts.length;i++) { const img = charts[i].getAs("image/png") .setName('chartdata' + i + '.png'); images['chart' + i] = img; body += '
'; } const options = { to: '…メールアドレス…', // ☆ subject: 'グラフの送信', htmlBody: body, inlineImages:images }; MailApp.sendEmail(options); } リスト7-5-1 function importInBox() { const result = []; const threads = GmailApp.getInboxThreads(); for(var i = 0;i < threads.length;i++) { const msgs = threads[i].getMessages(); for(var j = 0;j < msgs.length;j++) { const msg = msgs[j]; const msgdata = [ msg.getFrom(), msg.getSubject(), msg.getDate().toLocaleString() ]; result.push(msgdata); }; }; const sheet = SpreadsheetApp.getActiveSheet(); const range = sheet.getRange(1, 1, result.length, 3); range.setValues(result); } リスト7-7-1 function searchThreds() { const ui = SpreadsheetApp.getUi(); const res = ui.prompt('検索テキストを入力:', ui.ButtonSet.OK_CANCEL); if (res.getSelectedButton() == ui.Button.OK) { const find = res.getResponseText(); const result = []; const threads = GmailApp.search(find ,0, 100); for(var i = 0;i < threads.length;i++) { const msgs = threads[i].getMessages(); for(var j = 0;j < msgs.length;j++) { const msg = msgs[j]; const msgdata = [ msg.getFrom(), msg.getSubject(), msg.getDate().toLocaleString() ]; result.push(msgdata); }; }; const sheet = SpreadsheetApp.getActiveSheet(); const range = sheet.getRange(1, 1, result.length, 3); range.setValues(result); } } リスト8-3-1 function getCalendars(){ const sheet = SpreadsheetApp.getActiveSheet(); const cals = CalendarApp.getAllCalendars(); var n = 1; for (var i in cals){ var cal = cals[i]; sheet.getRange(n, 1).setValue(cal.getName()); sheet.getRange(n, 2).setValue(cal.getId()); sheet.getRange(n, 3).setValue(cal.getDescription()); sheet.getRange(n, 4).setValue(cal.getColor()); sheet.getRange(n, 5).setValue(cal.isSelected()); n++; } } リスト8-5-1 function getCalEvents(){ const sheet = SpreadsheetApp.getActiveSheet(); const cal = CalendarApp.getDefaultCalendar(); const d1 = new Date(); d1.setDate(1); d1.setHours(0); d1.setMinutes(0); d1.setSeconds(0); const d2 = new Date(); d2.setDate(1); d2.setMonth(d2.getMonth() + 1); d2.setHours(0); d2.setMinutes(0); d2.setSeconds(0); var evts = cal.getEvents(d1,d2); if (evts.length > 0){ const data = [['タイトル','説明','開始日','終了日']]; sheet.getRange(1, 1, 1, 4).setValues(data); for (var i in evts){ var j = i * 1 + 2; var evt = evts[i]; sheet.getRange(j, 1).setValue(evt.getTitle()); sheet.getRange(j, 2).setValue(evt.getDescription()); sheet.getRange(j, 3).setValue(evt.getStartTime()); sheet.getRange(j, 4).setValue(evt.getEndTime()); } } } リスト8-6-1 const d2 = new Date(); d2.setDate(1); d2.setMonth(d2.getMonth() + 3); // ☆ d2.setHours(0); d2.setMinutes(0); d2.setSeconds(0); リスト8-6-2 const d1 = new Date(); d1.setHours(0); d1.setMinutes(0); d1.setSeconds(0); const d2 = new Date(); d2.setDate(d2.getDate() + 100);// ☆ d2.setHours(0); d2.setMinutes(0); d2.setSeconds(0); リスト8-8-1 function setFilter() { const ui = SpreadsheetApp.getUi(); const sheet = SpreadsheetApp.getActiveSheet(); const re = ui.prompt("検索テキスト:", ui.ButtonSet.OK_CANCEL); if (re.getSelectedButton() == ui.Button.OK) { const range = sheet.getDataRange(); range.createFilter(); const filter = range.getFilter(); const criteria = SpreadsheetApp.newFilterCriteria() .whenTextContains(re.getResponseText()) .build(); filter.setColumnFilterCriteria(1, criteria); } } リスト8-9-1 function addEvents() { const range = SpreadsheetApp.getActiveRange(); const values = range.getValues(); const cal = CalendarApp.getDefaultCalendar(); for(var i in values) { var data = values[i]; var title = data[0].toString(); var d1 = data[2]; var d2 = data[3]; var op = {description:data[1]}; cal.createEvent(title, d1, d2, op); } } リスト8-10-1 function addAllDayEvents() { const range = SpreadsheetApp.getActiveRange(); const values = range.getValues(); const cal = CalendarApp.getDefaultCalendar(); for(var i in values) { var data = values[i]; var title = data[0].toString(); var d1 = data[2]; var op = {description:data[1]}; cal.createAllDayEvent(title, d1, op); } } リスト9-2-1 function getCovidData() { const sheet = SpreadsheetApp.getActiveSheet(); const url = 'https://www.stopcovid19.jp/data/covid19japan.json'; const response = UrlFetchApp.fetch(url); const re = response.getContentText(); const ob = JSON.parse(re); const datos = ob.area; sheet.getRange('A1:F1').setValues([['Name', '都道府県名', '累積陽性者数', '現在患者数', '死者数', '重症者数']]) for (var i in datos) { var data = datos[i]; sheet.getRange(i*1+2, 1).setValue(data['name']); sheet.getRange(i*1+2, 2).setValue(data['name_jp']); sheet.getRange(i*1+2, 3).setValue(data['npatients']); sheet.getRange(i*1+2, 4).setValue(data['ncurrentpatients']); sheet.getRange(i*1+2, 5).setValue(data['ndeaths']); sheet.getRange(i*1+2, 6).setValue(data['nheavycurrentpatients']); } } リスト9-6-1 function getNews() { const sheet = SpreadsheetApp.getActiveSheet(); sheet.getRange(1, 1, 1, 5).setValues([['提供元','見出し','内容','日時','リンク先']]) const url = 'https://news.google.com/news/rss/headlines/section/topic/WORLD?hl=ja&gl=JP&ceid=JP:ja'; const response = UrlFetchApp.fetch(url); const re = response.getContentText(); const ob = XmlService.parse(re); const doc = ob.getRootElement(); const ch = doc.getChild('channel'); const items = ch.getChildren('item'); for(var i in items) { var item = items[i]; var src = item.getChild('source').getText(); var title = item.getChild('title').getText(); var pub = item.getChild('pubDate').getText(); var link = item.getChild('link').getText(); var desc = item.getChild('description').getText().replace(/(<([^>]+)>)| /ig,''); sheet.getRange(i*1+2, 1, 1, 5).setValues([[src, title, desc, pub, link]]); } } リスト9-7-1 function getNews() { const sheet = SpreadsheetApp.getActiveSheet(); const url = 'https://news.google.com/news/rss/headlines/section/topic/WORLD?hl=ja&gl=JP&ceid=JP:ja'; const response = UrlFetchApp.fetch(url); const re = response.getContentText(); const ob = XmlService.parse(re); const doc = ob.getRootElement(); const ch = doc.getChild('channel'); const items = ch.getChildren('item'); sheet.insertRowsAfter(1,items.length); for(var i in items) { var item = items[i]; var src = item.getChild('source').getText(); var title = item.getChild('title').getText(); var pub = item.getChild('pubDate').getText(); var link = item.getChild('link').getText(); var desc = item.getChild('description').getText().replace(/(<([^>]+)>)| /ig,''); sheet.getRange(i*1+2, 1, 1, 5).setValues([[src, title, desc, pub, link]]); } } リスト9-9-1 function GetHtml() { const ui = SpreadsheetApp.getUi(); const res = ui.prompt('検索テキストを入力:'); const wd = res.getResponseText(); if (wd == ""){ return; } const url = "https://www.bing.com/search?q=" + wd; const response = UrlFetchApp.fetch(url); const re = response.getContentText(); const links = Parser.data(re).from('

Sample Web

リスト10-8-2 function doGet() { var output = HtmlService.createTemplateFromFile('index'); return output.evaluate(); } リスト10-11-1

Sample Web

までの合計は です。

リスト10-11-2 function getTotal(num) { var total = 0; for (var i = 1;i <= num;i++) { total += i; } return total; }