- Google スプレッドシートのApp Scriptを開いて translate.gs のコードを貼り付ける
- スプレッドシートの先頭行に以下のような形で言語名を記述する
| key👇 lang👉 | ja-JP | zh-CHS | en-US |
|---|
スプレッドシートの２行目以降に翻訳テキストを追加していく
１列目はKeyないしID、２列目以降は１行目に定義した言語ごとのテキスト
１行目、１列目を「表示 → 固定」で固定しておくと便利。
Created
August 30, 2024 08:21
-
-
Save tor4kichi/ca30ec8bd576028ceeeb5b77891722ad to your computer and use it in GitHub Desktop.
翻訳用スクリプト for i18nPortable【Google App Script】
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| const OUTPUT_FOLDER_ID = "YOUR_FOLDER"; | |
| function onOpen() { | |
| const ui = SpreadsheetApp.getUi(); // Uiクラスを取得する | |
| const menu = ui.createMenu('翻訳ツール'); // Uiクラスからメニューを作成する | |
| menu.addItem('全ての言語コードをkvpテキストとして出力', 'ExportCurrentLocalizeSheet'); // メニューにアイテムを追加する | |
| menu.addItem('現在の行の未翻訳列を仮翻訳(ja-JPを参照)', 'KariTranslationCurrentColumn'); // メニューにアイテムを追加する | |
| menu.addItem('選択中セル全てを仮翻訳(ja-JPを参照)', 'KariTranslationSelectedCells'); // メニューにアイテムを追加する | |
| menu.addToUi(); // メニューをUiクラスに追加する | |
| } | |
| const jaLocaleColumn = 2; | |
| function KariTranslationCurrentColumn() | |
| { | |
| const sheet = SpreadsheetApp.getActiveSheet(); | |
| const currentCell = SpreadsheetApp.getCurrentCell(); | |
| const currentColumn = currentCell.getColumn(); | |
| const locale = sheet.getDataRange().getCell(1, currentColumn).getValue().substring(0, 2); | |
| if (locale == null || locale == '') { return; } | |
| const range = sheet.getDataRange(); | |
| const values = range.getValues(); | |
| const rowCount = range.getLastRow() + 1; | |
| for (let row = 1; row < rowCount; row++) | |
| { | |
| const keyCellValue = range.getCell(row, 1).getValue(); | |
| if (keyCellValue == null || keyCellValue == '') { continue; } | |
| if (startsWith(keyCellValue, '#')) { continue; } | |
| const valueCell = range.getCell(row, currentColumn); | |
| if (valueCell.getValue() == '') | |
| { | |
| const jaText = range.getCell(row, jaLocaleColumn).getValue(); | |
| console.log(jaText); | |
| if (jaText == null || jaText == '') | |
| { | |
| continue; | |
| } | |
| const translatedText = LanguageApp.translate(jaText, 'ja', locale); | |
| console.log(translatedText); | |
| if (translatedText != null) | |
| { | |
| valueCell.setValue(translatedText.charAt(0).toUpperCase() + translatedText.slice(1)); | |
| valueCell.setBackground("#FFFF00"); | |
| } | |
| } | |
| } | |
| } | |
| function KariTranslationSelectedCells() | |
| { | |
| const sheet = SpreadsheetApp.getActiveSheet(); | |
| const selection = SpreadsheetApp.getSelection(); | |
| const ranges = selection.getActiveRangeList().getRanges(); | |
| for (let i = 0; i < ranges.length; i++ ) | |
| { | |
| const range = ranges[i]; | |
| const lastColumn = range.getLastColumn(); | |
| for (let currentColumn = range.getColumn(); currentColumn <= lastColumn; ++currentColumn) | |
| { | |
| // key行と日本語行は除外 | |
| if (currentColumn <= 2) { continue; } | |
| const currentCell = sheet.getDataRange().getCell(1, currentColumn); | |
| const localeText = currentCell.getValue(); | |
| if (localeText == null || localeText == '') { continue; } | |
| const locale = localeText.substring(0, 2); | |
| const rowIndex = range.getRowIndex(); | |
| const rowCount = range.getLastRow() - rowIndex + 1; | |
| Logger.log(rowIndex); | |
| const rowEnd = rowIndex + rowCount; | |
| Logger.log(rowEnd); | |
| for (let row = rowIndex; row < rowEnd; row++) | |
| { | |
| const keyCell = sheet.getRange(row, 1).getCell(1, 1); | |
| const keyCellValue = keyCell.getValue(); | |
| if (keyCellValue == null || keyCellValue == '') { continue; } | |
| if (startsWith(keyCellValue, '#')) { continue; } | |
| const valueCell = sheet.getRange(row, currentColumn).getCell(1, 1); | |
| const jaText = sheet.getRange(row, jaLocaleColumn).getCell(1, 1).getValue(); | |
| const translatedText = LanguageApp.translate(jaText, 'ja', locale); | |
| if (translatedText != null) | |
| { | |
| valueCell.setValue(translatedText.charAt(0).toUpperCase() + translatedText.slice(1)); | |
| valueCell.setBackground("#FFFF00"); | |
| } | |
| } | |
| } | |
| } | |
| } | |
| function ExportCurrentLocalizeSheet() { | |
| var sheet = SpreadsheetApp.getActiveSheet(); | |
| ExportLocalizeTextSheet(sheet); | |
| } | |
| function ExportLocalizeTextSheet(sheet) | |
| { | |
| var dataRange = sheet.getDataRange(); | |
| var values = dataRange.getValues(); | |
| var lastColumn = dataRange.getLastColumn(); | |
| for (let column = 2; column <= lastColumn; column++) | |
| { | |
| var kvp = SheetToKVPText(values, column); | |
| var filename = values[0][column - 1] + ".txt"; | |
| var folder = DriveApp.getFolderById(OUTPUT_FOLDER_ID); | |
| // remove if exists. | |
| var files = folder.getFilesByName(filename); | |
| var file; | |
| if (files.hasNext()) | |
| { | |
| file = files.next(); | |
| file.setContent(kvp); | |
| } | |
| else | |
| { | |
| // output | |
| folder.createFile(filename, kvp); | |
| } | |
| } | |
| } | |
| function SheetToKVPText(values, column) | |
| { | |
| let valueColumn = column - 1; | |
| var result = ''; | |
| for (var row = 1; row < values.length; row++) | |
| { | |
| if (values[row][0] == null || values[row][0] == '') | |
| { | |
| } | |
| else if (startsWith(values[row][0], '#')) | |
| { | |
| result += values[row][0]; | |
| } | |
| else if (values[row][valueColumn] != '') | |
| { | |
| result += values[row][0] + ' = ' + values[row][valueColumn]; | |
| } | |
| result += '\r\n'; | |
| } | |
| return result; | |
| } | |
| function startsWith(string, searchString) { | |
| return (string.indexOf(searchString) === 0); | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment