Replace All Commas with Blank in an Array

Question

I have hit a dead end - need some help! code is below. Should be very simple.... I have an array that is many rows long and 33 columns wide..... I just need to get all the commas out of the array values. The array is composed of some string fields and also some number fields..... Any help is appreciated!

So far I can't get any luck with the .replace function.... thanks

function Pipeline() {
  var writelocation = SpreadsheetApp.openById(globalvariables().importerid);
  var writesheet = writelocation.getSheetByName("Pipeline");
  var linkssheet = writelocation.getSheetByName(globalvariables().linkstab);
  writesheet.getRange("A:AG").clear();
  //var length = 1 + getLastRowSpecial(linkssheet.getRange("C:C").getValues());
  var length = 5;
  var array = [];
  var array = [
    ["Highlight", "Customer", "Prospect", "Type", "Status", "Product Detail & Comments", "Start Date", "End Date", "Bulk Sales", "Bulk GM%", "Non-Bulk Sales", "Non-Bulk GM%", "Safety Sales", "Safety GM%", "Welding Sales", "Welding GM%", "Rent / Other Sales", "Rent / Other GM%", "Source", "Segment", "Competitor", "Contract Expiration", "Win Reason", "Loss Reason", "PSA Won", "Contacts", "Probability", "Monthly Sales", "Monthly GM", "12M Sales", "District", "Area", "Salesperson"]
    ];    
  for (i=2; i<length; i++) {
    var source = SpreadsheetApp.openByUrl(linkssheet.getRange(i,3).getValue()).getSheetByName("Pipeline").getRange("A4:AG").getValues().filter(function(item){ return item[4] != ""; });

    for (k=0; k<source.length; k++) {
      for (j=0; j<source[k].length; j++) {
        source[k][j].toString().replace(/[,]/g,'-');  

      } 
      array.push(source[k]);
         }
  }
  writesheet.getRange(1,1,array.length,array[0].length).setValues(array);  
  linkssheet.getRange("E3").setValue(new Date()).setNumberFormat("yyyy-MM-dd HH:mm:ss");
}

Alpesh Jain · Accepted Answer · 2020-05-27 05:46:43Z

Your script has multiple issue-

You are declaring same variable twice-

var array = []; var array = ["Highlight",...]

You are setting header values in a 1D array, which can not be written to Sheet.

var array = [ ["Highlight", "Customer", "Prospect", "Type", "Status", "Product Detail & Comments", "Start Date", "End Date", "Bulk Sales", "Bulk GM%", "Non-Bulk Sales", "Non-Bulk GM%", "Safety Sales", "Safety GM%", "Welding Sales", "Welding GM%", "Rent / Other Sales", "Rent / Other GM%", "Source", "Segment", "Competitor", "Contract Expiration", "Win Reason", "Loss Reason", "PSA Won", "Contacts", "Probability", "Monthly Sales", "Monthly GM", "12M Sales", "District", "Area", "Salesperson"] ];

You are replacing "," with "-" when the title say you want to replace it with space
You are not assigning the replaced value to any other variable, the replace method doesn't changes the object it is called on.

So this is how your code should look like

function Pipeline() {
  var writelocation = SpreadsheetApp.openById(globalvariables().importerid);
  var writesheet = writelocation.getSheetByName("Pipeline");
  var linkssheet = writelocation.getSheetByName(globalvariables().linkstab);
  writesheet.getRange("A:AG").clear();
  //var length = 1 + getLastRowSpecial(linkssheet.getRange("C:C").getValues());
  var length = 5;
  var array = [];
  array[0] = [
    ["Highlight", "Customer", "Prospect", "Type", "Status", "Product Detail & Comments", "Start Date", "End Date", "Bulk Sales", "Bulk GM%", "Non-Bulk Sales", "Non-Bulk GM%", "Safety Sales", "Safety GM%", "Welding Sales", "Welding GM%", "Rent / Other Sales", "Rent / Other GM%", "Source", "Segment", "Competitor", "Contract Expiration", "Win Reason", "Loss Reason", "PSA Won", "Contacts", "Probability", "Monthly Sales", "Monthly GM", "12M Sales", "District", "Area", "Salesperson"]
  ];    
  for (i=2; i<length; i++) {
    var source = SpreadsheetApp.openByUrl(linkssheet.getRange(i,3).getValue()).getSheetByName("Pipeline").getRange("A4:AG").getValues().filter(function(item){ return item[4] != ""; });

    for (k=0; k<source.length; k++) {
      for (j=0; j<source[k].length; j++) {
        source[k][j] = source[k][j].toString().replace(/[,]/g,' ');  

      } 
      array.push(source[k]);
    }
  }
  writesheet.getRange(1,1,array.length,array[0].length).setValues(array);  
  linkssheet.getRange("E3").setValue(new Date()).setNumberFormat("yyyy-MM-dd HH:mm:ss");
}

Thank you, this solution solved the problem, specifically with processing the array correctly. Thanks again

Tanaike · Accepted Answer · 2020-05-27 00:58:47Z

How about this answer?

Modification points:

In your script, I think that source[k][j].toString().replace(/[,]/g,'-'); is required to be put to source[k][j].
- I think that the reason of your issue might be this.
Your title is Replace All Commas with Blank in an Array. But, replace(/[,]/g,'-') replaces , to -. Although I'm not sure about the actual value of source[k][j].toString(), if you want to replace , to blank, please modify to replace(/[,]/g,' ') or replace(/[,]/g,'').
- I think that you want to replace only ,, you can also use replace(/,/g,' ')

When above points are reflected to your script, it becomes as follows.

Modified script:

From:

source[k][j].toString().replace(/[,]/g,'-');

To:

source[k][j] = source[k][j].toString().replace(/[,]/g,' ');  // or replace(/[,]/g,'')

Reference:

replace()

Cooper · Accepted Answer · 2020-05-27 02:40:25Z

function Pipeline() {
  var writelocation = SpreadsheetApp.openById(globalvariables().importerid);
  var writesheet = writelocation.getSheetByName("Pipeline");
  var linkssheet = writelocation.getSheetByName(globalvariables().linkstab);
  writesheet.getRange(1,1,writesheet.getLastRow(),33).clear();
  var array = [["Highlight", "Customer", "Prospect", "Type", "Status", "Product Detail & Comments", "Start Date", "End Date", "Bulk Sales", "Bulk GM%", "Non-Bulk Sales", "Non-Bulk GM%", "Safety Sales", "Safety GM%", "Welding Sales", "Welding GM%", "Rent / Other Sales", "Rent / Other GM%", "Source", "Segment", "Competitor", "Contract Expiration", "Win Reason", "Loss Reason", "PSA Won", "Contacts", "Probability", "Monthly Sales", "Monthly GM", "12M Sales", "District", "Area", "Salesperson"]];    
  var vs=linksheet.getRange(2,3,3,1).getValues();
  vs.forEach(function(url,i){
    var sss=SpreadsheetApp.openByUrl(url);
    var ssh=sss.getSheetByName("Pipeline");
    var srg=ssh.getRange(4,1,ssh.getLastRow()-3,1);
    var source=srg.getValues();
    source.forEach(function(r,k){
      r.forEach(function(c,j){
        source[k][j].replace(/[,]/g,'');
      });
      array.push(source[k])
    });   
    writesheet.getRange(1,1,array.length,array[0].length).setValues(array);  
    linkssheet.getRange("E3").setValue(new Date()).setNumberFormat("yyyy-MM-dd HH:mm:ss");
  });
}

Collectives™ on Stack Overflow

Replace All Commas with Blank in an Array

3 Answers 3

1 Comment

Modification points:

Modified script:

Reference:

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Modification points:

Modified script:

Reference:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related