Created
August 17, 2020 01:36
-
-
Save tor4kichi/49d178d266a560c7c85bbc3a13406852 to your computer and use it in GitHub Desktop.
Googleスプレッドシート向けの翻訳作業補助スクリプト
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 = "PUT_HERE_YOUR_GOOGLE_DRIVE_FOLDER_ID"; | |
| function onOpen() { | |
| const ui = SpreadsheetApp.getUi(); // Uiクラスを取得する | |
| const menu = ui.createMenu('Translation Tools'); // 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); | |
| 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); | |
| 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