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

ラベル Spreadsheet の投稿を表示しています。 すべての投稿を表示
ラベル Spreadsheet の投稿を表示しています。 すべての投稿を表示

2025年6月12日木曜日

スプレッドシートの空白セルを直前の値で埋めたい


A列の空白セルに直前の値を入れたくて書いたコードです


スプレッドシートに以下のようなBeforeの表があるとき
（A列に空白セルがある）


Before
1AB
2エリア都市
3東京新宿
4渋谷
5池袋
6神奈川横浜
7川崎
8相模原
9千葉千葉
10船橋
11松戸



空白セルの直前に入っている値で埋めて以下Afterのようにしたい


After
1AB
2エリア都市
3東京新宿
4東京渋谷
5東京池袋
6神奈川横浜
7神奈川川崎
8神奈川相模原
9千葉千葉
10千葉船橋
11千葉松戸



Code.gs
function fillEmptyAreaCells() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const range = sheet.getRange("A2:A" + sheet.getLastRow()); // A2から最終行まで
const values = range.getValues();

let lastValue = "";

for (let i = 0; i < values.length; i++) {
if (values[i][0] !== "") {
lastValue = values[i][0]; // 新しい値を記憶
} else {
values[i][0] = lastValue; // 空欄を直前の値で埋める
}
}

range.setValues(values); // 上書き保存
}


Tips


スプレッドシートのURLを指定して実行したい場合


getSheetById(id) という待望のメソッドが追加されていたのでそれを使う

function fillEmptyAreaCellsBySheetUrl() {
const sheetUrl = "SPREADSHEET_URL";

// URLからgidを文字列として正確に取得（指数表記防止）
const gid = (sheetUrl.match(/gid=(\d+)/) || [])[1];
Logger.log("gid: " + gid);

const ss = SpreadsheetApp.openByUrl(sheetUrl); // ここでURLのスプレッドシートを明示的に開く
const sheet = ss.getSheetById(gid); // このSpreadsheetの中からgidを探す

if (!sheet) {
throw new Error("❌ 指定された gid のシートが見つかりません: " + gid);
}

const range = sheet.getRange("A2:A" + sheet.getLastRow());
const values = range.getValues();

let lastValue = "";

for (let i = 0; i < values.length; i++) {
if (values[i][0] !== "") {
lastValue = values[i][0];
} else {
values[i][0] = lastValue;
}
}

range.setValues(values);
}




今回シートのIDは const gid = (sheetUrl.match(/gid=(\d+)/) || [])[1]; で取得しています

const gid = sheetUrl.split("gid=")[1] ではエラーになりました



スプレッドシートのURLをよく見ると

シートIDが末尾に2つある
https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit?gid=127068302#gid=127068302

しかも#でつながっているので

const gid = sheetUrl.split("gid=")[1] とかやると

127068302#

末尾に#がついてきてエラーになる


Error: ❌ 指定された gid のシートが見つかりません: 1621369581#



Reference

getSheetById(id)


投稿者
ラベル:

2024年6月10日月曜日

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.



Procedures

SpreadsheetのA1〜A3にそれぞれ以下の値を入れておきます。
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 Code.gs in Google Slides Apps Script.


SPREADSHEET_IDは読み込ませたい値が入ったシートのIDに置き換えます。
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


Code.gs
const ssUrl = "https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0";

function modalessTemplate() {
const htmlOutput = HtmlService
.createTemplateFromFile("index")
.evaluate()
.setWidth(360)
.setHeight(240);
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";
}


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

<script>
function elem(id) {
return document.getElementById(id);
}

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

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

getList();
function getList() {
google.script.run
.withFailureHandler(onFailure)
.withSuccessHandler(createDatalist)
.returnList();
}

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

function submitClicked() {
const newValue = elem("tb").value;
google.script.run
.withFailureHandler(onFailure)
.withSuccessHandler(successSetNewValue)
.withUserObject(newValue)
.setNewValue(newValue);
}

function successSetNewValue(message, newValue) {
console.log(message);
if(message === "success") {
const datalist = elem("sheetValues");
const option = document.createElement("option");
option.textContent = newValue;
datalist.appendChild(option);
}
}
</script>
</body>
</html>

Reference

シートのデータをdatalistに読み込みたい

スプレッドシートのurlとnameをHTMLServiceでセレクトボックスのオプションに保存したい

投稿者
ラベル: , ,

2024年6月9日日曜日

Apps Scriptでスプレッドシートのセルを結合して上揃えにしたい - Merge cells and align top in Spreadsheet using Apps Script


スプレッドシートのセルを結合して垂直方向の配置を設定するコードを試しました。
I tried Apps Script to merge cells and set the vertical alignment in a spreadsheet.


今回の例では、
In this example,

3列目の4行目と5行目のセルを結合します。
I will merge the cells in the 3rd column, 4th and 5th rows.

垂直方向の配置は「上」に設定します。
The vertical alignment will be set to "top."

結合したセルにvalueという値を入力します。
The merged cell will be filled with the value "value."


Apps Script


Code.gs
function mergeCellsInColumnU() {
const value = "value";
const sheet = SpreadsheetApp.getActiveSheet();
const startRow = 4;
const startCol = 3;
const numRows = 2;
const numCols = 1;
const range = sheet.getRange(startRow, startCol, numRows, numCols);
range.merge().setVerticalAlignment("top").setValue(value);
}

Tips

選択範囲で行単位、列単位で結合するメソッドもあったので試してみました。
I also tried using the methods that allow merging cells by row or column within a selected range.


mergeAcross()
function mergeCellsInColumnU() {
const value = "value";
const sheet = SpreadsheetApp.getActiveSheet();
const startRow = 4;
const startCol = 3;
const numRows = 2;
const numCols = 3;
const range = sheet.getRange(startRow, startCol, numRows, numCols);
range.mergeAcross().setVerticalAlignment("top").setValue(value);
}



mergeVertically()
function mergeCellsInColumnU() {
const value = "value";
const sheet = SpreadsheetApp.getActiveSheet();
const startRow = 4;
const startCol = 3;
const numRows = 2;
const numCols = 3;
const range = sheet.getRange(startRow, startCol, numRows, numCols);
range.mergeVertically().setVerticalAlignment("top").setValue(value);
}

Reference

Class Range > merge()

Class Range > mergeAcross()

Class Range > mergeVertically()

投稿者
ラベル:

Latest post

Google Formsでクイズを作りたい

Googleフォームには回答を判定するクイズモードがあります 今回はそのクイズモードで回答の判定とフィードバックについて書いていきます 「クイズモード」の表記： 日本語の表記は「テストにする」ですが 英語の表記は「Make this a quiz」となっています この記事ではそれ...