Google Sheets split function with additional calculations

user_4488338 0
Viewed 96

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: https://docs.google.com/spreadsheets/d/1tvGKMTIuwVFlf7_ZEW8uHrXpdu3LWFiXcYyPj3pMt-M/edit?usp=sharing

One aspect that needs to happen is translating the format after splitting the string of the hour:minute:second:frame into hour:minute:second:millisecond. Generally these are for 30fps videos, so if 30f = 1000ms, then 10 is 1/3 of that

Original (hour:minute:second,frame) Translated (hour:minute:second:millisecond)
00:00:02,333 00:00:02:10

Ultimately (and as show in the sheet), I'm trying to get the following output from this input:

Input | | | -------- | | 1 | row | | 00:00:02,000 --> 00:00:05,000 | | Lorem ipsum dolor sit amet, consectetur adipiscing e | | | 2 | | 00:00:05,001 --> 00:00:08,000 | | Felis eget nunc lobortis mattis. Morbi tristique senectus et netus et malesuada. |

Output

TIMESTAMP ORIGINAL TEXT TRANSLATED
00:00:02:00 abc sdfsdfsdf Lorem ipsum dolor sit amet, consectetur adipiscing e
00:00:05:00 abc sdfsdfsdf Felis eget nunc lobortis mattis. Morbi tristique senectus et netus et malesuada.

Is this possible?

google-apps-script google-sheets
1 Answers

If you want to convert the time with frame to time with millisecond, use this formula:

Formula:

=REGEXREPLACE(A1,",\d+.*", ":"&text(round(REGEXEXTRACT(A1, ",(\d+)\s+-")/1000*30, 0), "00"))
  • text(number, "00") forces to have two numbers at the end.
  • round(number, 0) removes decimal

Output:

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:

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)
user_161186860
Related