I'm trying to create a worksheet to help our creative team with caption files. I would love them to be able to paste in their text file into a specific section of a Google Sheet that is then translated into an output they can use for caption translations and uploads.

This is an example of what directly pasting the file with no additional formatting, and the desired output:

Note:

• I avoided using split as it sometimes treats the time part as actual time so it gives its value instead of the string format. That's why you'll see me using regex functions instead.

EDIT:

• If you are open to apps script option, see the script below:

Script:

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);
}

Output:

Note:

• Table lines are not included in the script output
• 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)