Google Apps Script Removing Commas

Removing commas from form submissions can be tricky. Certain question types can save the responses in a 1,2,3,4 type format. If you find that you want to remove the commas for each submission that comes in then follow the steps below to remove them via Google Apps Script.

1. Open up the Google Spreadsheet where you are saving your responses and select Tools->Script Editor.

2. Copy and paste the following google apps script removing commas code in to the Script editor:

//get last row and last record to be modified
function getLastRow() {

//Get Spreadsheet Data
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
var lastRowRange = sheet.getRange(lastRow, 1, 1,lastColumn);
var lastRowValue = lastRowRange.getValues();
//Remove Commas
//you can identify additional columns here i.e. modColumn2 = 4
//lastRowValue[0][modColumn2-1] = removeComma(lastRowValue[0][modColumn2-1]);
var modColumn1 = 2;
lastRowValue[0][modColumn1-1] = removeComma(lastRowValue[0][modColumn1-1]);

//Set the cell format to plain text to avoide strange formatting issues
var modColumLetter = sheet.getRange(lastRow,modColumn1,1,1).getA1Notation();
sheet.getRange(modColumLetter).setNumberFormat('@STRING@');

//Additional Columns
//var modColumn2 = 2;
//lastRowValue[0][modColumn2-1] = removeComma(lastRowValue[0][modColumn2-1]);
//Set the cell format to plain text to avoide strange formatting issues
//var modColumLetter = sheet.getRange(lastRow,modColumn2,1,1).getA1Notation();
// sheet.getRange(modColumLetter).setNumberFormat('@STRING@');

//Save values back to spreadsheet
lastRowRange.setValues(lastRowValue);

};

//uses regex to remove comma
function removeComma(value){
var remComma = value.toString();
value = remComma.replace(/,/g,'');
return value;
};

3. Adjust the highlighted code above to match your situation. var ModColumn1 is the column of the spreadsheet that needs to be modified. Just change the number 2 to what ever you need. If you need to do additional columns just uncomment the highlighted code that starts with modColumn2. If you need even more, just copy and paste the Additional Columns code and increase the number of modColumn2 to modColumn3 and so on.

4. Authorize the script to run by clicking on the “Play” button (if needed):