Apps Script公式リファレンス: Apps Script Reference |障害・課題追跡: IssueTracker |Google Workspace: Status Dashboard - Summary


Google SlidesのダイアログにSpreadsheetの値を読み込みたい(datalist) - load the values from the Spreadsheet into a dialog in Google Slides.

Google Slidesのダイアログに配置したdatalistに、Spreadsheetの値を入れる機能を作ったときの備忘録です。
This is a memo for when I created a function to load values from a Spreadsheet into the options of a datalist placed in a dialog in Google Slides.


Enter the following values into cells A1 to A3 of the spreadsheet.
A1: option 1
A2: option 2
A3: option 3

Google SlidesのApps Scriptに以下のCode.gsを書きます。
Write the following code in in Google Slides Apps Script.

Replace SPREADSHEET_ID with the ID of the sheet containing the values you want to load.

modalessTemplate() を実行すると、ダイアログに配置したデータリストにシートの値が読み込まれます。
Execute the modalessTemplate() to load those data into a data list placed in a dialog in Google Slides.

リストにない option 4 を入力してsubmitをクリックします。
Enter option 4, which is not in the list, and click submit.

シートに option 4 が追加されます。
Option 4 will be added to the sheet.

ダイアログリストにも option 4 が追加されます。
The option 4 will also be added to the list in the dialog.

Apps Script
const ssUrl = "";

function modalessTemplate() {
const htmlOutput = HtmlService
SlidesApp.getUi().showModelessDialog(htmlOutput, "MyGUI");

function returnList() {
const sheet = SpreadsheetApp.openByUrl(ssUrl).getSheets()[0];
const values = sheet.getDataRange().getValues();
const list = values.flat();
return list;

function setNewValue(newValue) {
const sheet = SpreadsheetApp.openByUrl(ssUrl).getSheets()[0];
const lastRow = sheet.getLastRow();
const data = sheet.getRange("A1:A" + lastRow).getValues();
for (let i = 0; i < data.length; i++) {
if (data[i][0] == newValue) {
return "failure: value already exists";
sheet.getRange("A" + (lastRow + 1)).setValue(newValue);
return "success";

<!DOCTYPE html>
<base target="_top">
<input type="text" id="tb" list="sheetValues">
<button id="submit">submit</button>
<datalist id="sheetValues"></datalist>

function elem(id) {
return document.getElementById(id);

function onFailure(e) {
alert([e.message, e.stack]);

elem("submit").addEventListener("click", submitClicked);

function getList() {

function createDatalist(list) {
const datalist = elem("sheetValues");
for(let i = 0; i < list.length; i++) {
const option = document.createElement("option");
option.textContent = list[i];

function submitClicked() {
const newValue = elem("tb").value;

function successSetNewValue(message, newValue) {
if(message === "success") {
const datalist = elem("sheetValues");
const option = document.createElement("option");
option.textContent = newValue;




Latest post

Google Apps Scriptの障害時はIssueTrackerを見てみる - Incidents for Apps Script are reported on Issue Tracker

IssueTracker > Apps Script issues Google Apps Scriptの障害時は IssueTracker に課題が上がっていることが...