Просто заметки: SpreadsheetApp.

timestamp,name,text,initials 2010-04-18 08:01:54,PIT,Lewis last car's coming into position now.,PW 2010-04-18 08:02:05,PIT,All cars in position.,PW 2010-04-18 08:02:59,COM,0802: The race has started,CM

My first thought was to just format it in Excel but quickly got frustrated with the way it handles dates/time, so instead uploaded it to Google Spreadsheet. Shown below is how the same data appears:

Google Spreadsheet of csv

Having played around with the timed-text XML format I knew the goal was to convert each row into something like (of course wrapping with the obligatory XML header and footer):

<p style="s1" begin="00:00:00" id="p1" end="00:00:11">PIT: Lewis last car's coming into position now.</p>

Previously I've played with Google Apps Script to produce an events booking systems, which uses various components of Google Apps (spreadsheet, calendar, contacts and site), so it made sense to use the power of Scripts for timed text. A couple of hours later I came up with this spreadsheet (once you open it click File –> Make a copy to allow you to edit).

On the first sheet you can import your timed data (it doesn't have to be *.csv, it only has to be readable by Google Spreadsheet), and then clicking 'Subtitle Gen –> Timed Data to XML' on the XMLOut sheet it generates and timed text XML.

Below is the main function which is doing most of the work, the comments indicating what's going on:

01function writeTTXML() { 
02var ss = SpreadsheetApp.getActiveSpreadsheet(); 
03var dataSheet = ss.getSheets()[0]; 
04var data = getRowsData(dataSheet); // read data from first sheet into javascript  object
05var sheet = ss.getSheetByName("XMLOut") || ss.insertSheet("XMLOut"); // if there isn't a XMLOut sheet create one 
06sheet.clear(); // make sure it is blank 
07// Start the XMLOut  sheet with tt-XML doc header
08sheet.getRange(1, 1).setValue("<?xml version=\"1.0\"  encoding=\"utf-8\"?><tt xmlns=\"http://www.w3.org/2006/10/ttaf1\"  xmlns:ttp=\"http://www.w3.org/2006/10/ttaf1#parameter\"  ttp:timeBase=\"media\" xmlns:tts=\"http://www.w3.org/2006/10/ttaf1#style\"  xml:lang=\"en\" xmlns:ttm=\"http://www.w3.org/2006/10/ttaf1#metadata\"><head><metadata><ttm:title>Twitter  Subtitles</ttm:title></metadata><styling><style  id=\"s0\" tts:backgroundColor=\"black\" tts:fontStyle=\"normal\"  tts:fontSize=\"16\" tts:fontFamily=\"sansSerif\" tts:color=\"white\"  /></styling></head><body tts:textAlign=\"center\"  style=\"s0\"><div>");
09var startTime = data[0].timestamp; // collect start time from first data row, all  subsequent relative to this
10for (var i = 0; i < (data.length-1); ++i) { // looping through all the data one row at a  time except last line (excluded because have no end date/time
11var row = data[i]; 
12var nextRow = data[i+1]; 
13row.rowNumber = i + 1;
 14//calc begin and end for  an entry converting to HH:mm:ss format.
15var begin = Utilities.formatDate(new Date(row.timestamp-startTime), "GMT", "HH:mm:ss"); 
16var end = Utilities.formatDate(new Date(nextRow.timestamp-startTime), "GMT", "HH:mm:ss"); 
17// prepare string in  tt-XML format. Conent is pulled by ref the column header in normalised  format (e.g. if col headed 'Twitter status' normalsed = 'twitterStatus'
18 var str = "<p  style=\"s1\" begin=\""+begin+"\" id=\"p"+row.rowNumber+"\" end=\""+end+"\">"+row.name+": "+row.text+"</p>";; 
19// add line to XMLOut  sheet
20var out = sheet.getRange(row.rowNumber+1, 1).setValue(str); 
21}
 22var lastRow = sheet.getLastRow()+1;
 23//write tt-XML doc  footer
24var out = sheet.getRange(lastRow, 1).setValue("</div></body></tt>"); 
25}

If your timed data has different headers you can tweak this by clicking 'Tools –> Script –> Script editor …' and changing how the str on line 18 is constructed.

I'm the first one to admit that this spreadsheet isn't the most user friendly and it only includes the tt-XML format, but hopefully there is enough structure for you to go, play and expand (if you do please use the post comments to share your findings)

Просто заметки

понедельник, 26 апреля 2010 г.

SpreadsheetApp.

Комментариев нет:

Отправить комментарий

понедельник, 26 апреля 2010 г.

SpreadsheetApp.

Комментариев нет:

Отправить комментарий

понедельник, 26 апреля 2010 г.