01 Translation

1. Download English Subtitle (.srt) from dotSUB

Click "English"


2. Import Subtitle (.srt) into Google Docs

Open English subtitle file (_eng.srt) with a text editor.
Select all the text. (Ctrl + A)
Copy all the text. (Ctrl + C)

Open template spreadsheet in Google Docs. (You can rename the template.)
Double click cell A1 of sheet "in". (Not just select the cell.)
Paste the text into cell A1. (Ctrl + V)

On sheet "main", data are automatically arranged.
If "working ..." has been showing for more than 5 seconds since you selected sheet "main", click "Google docs" logo or close your browser, and reopen the spreadsheet.


3. Convert Formulas to Values

Select columns A to E.
Copy the columns. (Ctrl + C)

Select "Paste values only".
At this time, the contents are converted from formulas to values.
Even if sheet "in" is renewed after this step, it doesn't affect sheet "main".


4. Translate

Type translation into cells in column E.
Avoid editing cells in columns A to C.

Columns to the right of E are usable for any purpose.
Type labels in row 1, if needed.
For example:

F: "dif"
difference between a start time and an end time

G: "len"
the number of letters of each sentence in column E

H: per
the number of letters per second
Under "format", select "Change calors with rules ..." to change the rule.

I: old version of translation

(You can delete these columns prepared.)


5. Export Subtitle (.srt)

A menu "Export (.srt)" is automatically made when this template is opened.

Script embedded is:

function onOpen() {
  var thisSS = SpreadsheetApp.getActiveSpreadsheet();
   var myFs = [
     {name: "Export (.srt) to your Docs List.", functionName: "exportTranslation"},
   ];
   thisSS.addMenu("Export(.srt)", myFs);
}
 
function exportTranslation(){
  var thisSS = SpreadsheetApp.getActiveSpreadsheet();
  var sheetA = thisSS.getSheetByName("a");
  var myName = Browser.inputBox("The file named here will be appeared in your Docs list.", Browser.Buttons.OK_CANCEL);
  if (myName == "cancel"){
  } else {
    DocsList.createFile(myName + ".srt", sheetA.getRange("K2").getValue());
  }
}

Under "Export (.srt)", select the "Export (.srt) to your Docs List.".

The following window will be appeard.
Read the alert.
Only if you trust the script above, click "OK".


Select the "Export (.srt) to your Docs List." again.
Fill in a name.

A subtitle (.srt) file well be appeard in a few seconds.
Click the file created.

Click "Download (...KB)" to download the file.
--------------------------------------------------------------------------------------------
another way to export subtitle (.srt) file
--------------------------------------------------------------------------------------------
In real time, SRT data in cell A1 of sheet "out" is being renewed automatically.
(The line break used is CHAR(10).)
Select  cell A1.
Copy cell A1.

Select cell B1.
Select "
Paste value only".

Double click cell B1. (Not select the cell.)
Select all the text in cell B1. (Ctrl + A)
Copy all the text in cell B1. (Ctrl + C)

Paste the text in a text editor. (Ctrl + V)

Save it as UTF-8.


6. Import Subtitle (.srt) into dotSUB


Comments