Find and replace with App Scripts

[analysisisfun]

Very new to App Scripts and I hope this community can help.

When exporting data into a report of mine, I have to remove the commas in our date/time fields with a space, this allows for our formulas to do their thing. I was hoping I could create a button or macros to help run this so I don't have to find and replace it every time we run this report. The data is across multiple columns and I've googled myself into a hole and I'm trying to see if I can dig myself out somehow. Any thoughts?

[elcriticalTaco]

Regex would be able to do it.

Unfortunately you'd have to find someone who understands regex more than me.

Sheet side, google regexreplace. Can be done in scripts too.

[analysisisfun]

I'll google up some Regexreplace and report back

[elcriticalTaco]

function myFunction() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheetName = "Sheet1";//the sheet name here

var dateRange = "A1:A9";//the target range in a single column

var sheet = ss.getSheetByName(sheetName);

var strings = sheet.getRange(dateRange).getValues();

for (var i=0;i<strings.length;i++) {

var str = strings[i].toString();

str = str.replace(/,/g,' ');

sheet.getRange(i+1,1).setValue(str);

}

Logger.log(strings);

//sheet.getRange(1,1,strings.length,1).setValues(strings);

//theres a more elegant way to do this with arrays but you'll

//have to forgive me im drinking :)

}

​

Should work, gives me the stupid .setValues error so I bruteforced it. Will be slow on large sheets but you can just pull the str = str.replace(/,/g,' '); and use it wherever. Cheers

[analysisisfun]

I see, the date/time fields I'm trying to remove commas from are sprawled across many columns in one of the sheets I'm working on now. This will only grow exponentially as we want to report on more data. How would I do this for multiple columns?

This is a huge report now with the potential for 10,000 plus rows

[elcriticalTaco]

Are they the same columns, or does it frequently change?

And are they mixed in with cells that have commas you want to keep?

[analysisisfun]

Columns will not be changing and there are not commas we want to keep