Substring from an array

[Stéphane Gallet]

Hi everyone,

I've tried a script that transform a timecode like that :

01:05:24:08

(in a world where the frame per second is 25) so fps=25

to a total frames count :

98108

I extract the hours, minutes, seconds and frames with a substring method and then I multiply them like that :

h = 01 x 3600 x 25

m = 05 x 60 x 25

s = 24 x 25

f = 08

The following script is ok :

function xconstant() {

var timecode = "01:05:24:08"

var fps = 25

var h, m, s, f, totalframes

var h = timecode.substr(0, 2)

var m = timecode.substr(3, 2)

var s = timecode.substr(6, 2)

var f = timecode.substr(9, 2)

var totalframes = h * (3600 * fps) + m * (60 * fps) + s * fps + f * 1

Logger.log(totalframes)

But in the real conditions (from a list of timecodes inside my column 33, the substring method doesn't work anymore.

Here's the script :

function xvariable() {

var application = SpreadsheetApp;

var feuille = application.getActiveSpreadsheet().getActiveSheet();

startRow = 4

var endRow = feuille.getLastRow();

var fps = 25

var h, m, s, f, loop, timecode, totalframes, totalframesarray = []

for (loop = startRow; loop <= endRow; loop++) {

timecode = feuille.getRange(loop, 33).getValues();

//var h = timecode.substr(0, 2)

//var m = timecode.substr(3, 2)

//var s = timecode.substr(6, 2)

//var f = timecode.substr(9, 2)

//var totalframes = h * (3600 * fps) + m * (60 * fps) + s * fps + f * 1

// totalframesarray.push(totalframes)

}

// feuille.getRange(startRow, 24, totalframesarray.length, 1).setValues(totalframesarray)

Logger.log(totalframes)

I could imagine that the problem is the nature of the variables, string versus numbers.

I'm trying to understand so what is wrong in my script ?

Thank to teach me the good way :)

Steph.

[Michael O'Shaughnessy]

Hello Steph...

I am by no means an expert and I am sure someone else will offer some better advice... But in my experience what you are running into is (1) getValues() returns a 2d array and (2) you are using "math" on a string.

I suggest that if you are getting only the value of ONE cell than use "getValue()"  NOT "getValues()"

Take a look at this quick code snippet: (NOTE: the values in cell A2 of Sheet 3 is 10:25:04:08)
  let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3");
  let data = ss.getRange(2,1,1,1).getValue();
  let newData = data.toString();
  console.log(newData);
  let h = newData.substr(0,2);
  console.log(h);
  let q = parseInt(h)+200;
  console.log(q);

You get "data" then convert it to a "string" so that you can use the "substr" function.  BUT, you have to use "parseInt()" to turn the 'string' into a 'number' so you can do math with it.

Like I said, I am sure there is another way to do this (ie you can use "string.split()" instead of substr, you can then use "forEach" to do the loop....) BUT using my suggestions you will make your script work they way you will understand it...  In time you will use other methods!

Hope this helps!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b46e1211-54d6-47c6-95cd-782b6c3f2bb9o%40googlegroups.com.

[Stéphane Gallet]

Hello Michael,

It was perfect for me, I've just used the following :

xstring = xvariable.toString()

var h = xstring.substr(0, 2)

And now it works for me.

Thanks a lot !! 

 

[Michael O'Shaughnessy]

Fantastic!  Glad to have helped!

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/2bd52d44-b51e-4852-9a5d-d900bc3f57d9o%40googlegroups.com.