google sheet apps script truncate column value

Question

I have the following table:

I am simply trying to write a Google apps script to insert into column E everything in column B prior to '.Upload'. So in the table column E = 20ba4a5c.

I think I should be able to use the split() function to do that but I'm having some difficulty.

ss.getRange('E'+lastRow).setFormula('SPLIT(B'+lastRow+'.Upload')[0]');

Answer 1

• You should use REGEXEXTRACT to build a simple regex in order to achieve your goal.

• It is also a better practice to use template literals when dealing with multiple concatenations.

Solution:

Replace:

setFormula('SPLIT(B'+lastRow+'.Upload')[0]');

with:

setFormula( `REGEXEXTRACT(B${lastRow},"^(.*?).Upload")`)

Output: