我正在嘗試創建一個作業表來幫助我們的創意團隊處理字幕檔案。我希望他們能夠將文本檔案粘貼到 Google 表格的特定部分，然后將其翻譯成可用于字幕翻譯和上傳的輸出。
這是直接粘貼檔案而不使用其他格式以及所需輸出的示例：
筆記：
- 我避免使用，
split因為它有時將時間部分視為實際時間，因此它給出了它的值而不是字串格式。這就是為什么你會看到我使用正則運算式函式。
編輯：
- 如果您對應用程式腳本選項開放，請參閱下面的腳本：
腳本：
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
// Expecting data is to be pasted at C6:C
var range = sheet.getRange("C6:C");
var values = range.getValues().flat().filter(Boolean);
// Expected succession of the data in C6:C is as follows:
// <number>
// <time>
// <subtitles>
// Doesn't matter how many empty rows in between as flat().filter(Boolean) will remove those rows
var data = [];
values.forEach(function(cell, index) {
if (index % 3 == 1)
// Format data if cell is expected to be time
data.push(cell.replace(/,(\d )\s.*/, ":" Math.round(cell.match(/,(\d )\s.*/)[1]/1000*30).toString().padStart(2, "0")));
if (index % 3 == 2)
// If cell is subtitles, add it beside the time together with "ORIGINAL TEXT"
// But since I'm not sure where "ORIGINAL TEXT" comes from or how it is generated, I'm leaving it blank for now
data[data.length - 1] = [data[data.length - 1], "", cell];
});
// Add headers at the start of the data
data.unshift(['TIMESTAMP', 'ORIGINAL TEXT', 'TRANSLATED']);
// Output starts at I6
sheet.getRange(6, 9, data.length, data[0].length).setValues(data);
}
輸出：
筆記：
- 腳本輸出中不包含表格行
- ORIGINAL TEXT is blank as I am still not sure where it comes from. But if you want a text machine translated, the script can also do that. (But I'm assuming this column needs to be done manually)
- It's up to you how you trigger this one. You can still manually run it but it's better if you can trigger it. You have several options when it comes to triggers:
- when a certain range is edited (e.g. text is pasted on it)
- at a specific time of the day (time trigger)
- button press (assign script to a button)
- or as a custom menu (menu on toolbar)
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/355710.html