Creating text string in google script

Question

I'm trying to do what I thought was basic script. but alas i'm struggling.

In Column C I am trying to create a string of Column D & Column E with a space in the middle. as a formula I would use

=D18&" "&E18

as code I have

{function myFunction() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("CP");
var cell = sheet.getRange("C:C");
cell.setFormula("=D:D&E:E");}

which does combine the two cells but without a space. is this at all possible. It needs to be done in script because I have third party services connected to google sheets where the data comes in but the formula does not carry down.

Thanks Dan

JPV · Accepted Answer · 2018-12-15 12:40:31Z

2

Try

cell.setFormula('=Arrayformula(D:D&" "&E:E)')

answered Dec 15, 2018 at 12:40

JPV

27.4k4 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Hardy Over a year ago

I tried this and although formula was brought through correctly as an array it errored because it was referencing itself

JPV Over a year ago

You can not use a formula referencing columns in those columns itself. The output should be okay when entered in column F (or any other column).

Cooper · Accepted Answer · 2018-12-15 18:52:35Z

0

Try this:

function cde(){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('CP');
  var rg=sh.getRange(1,3,sh.getLastRow(),3);
  var vA=rg.getValues();
  for(var i=0;i<vA.length;i++){
    vA[i][0]=String(vA[i][1]) + " " + String(vA[i][2]);
  }
  rg.setValues(vA);
}

edited Dec 15, 2018 at 18:52

answered Dec 15, 2018 at 18:37

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Collectives™ on Stack Overflow

Creating text string in google script

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related