Concatenate Randbetween with Numbers in Google Scripts

Tags:
Concatenate
JavaScript

I am working in Google sheets script editor, and trying to do the following (but only in google scripts):

=CONCATENATE("61", RANDBETWEEN(1000000000, 9999999999), "@text.com")

The output will be similar to what follows in a single cell:

617876453299@text.com

When I concatenate numbers and texts in scripts I am using the following formula:

SpreadsheetApp.getActiveSheet().getRange('A1:Z10').setValue(61 + "@text.com")

Which gives the appropriate output:

61@text.com

I have this code to generate random numbers and fill them in cells:

var sheet = SpreadsheetApp.getActive().getSheetByName("sheet1");
var range = sheet.getRange("A1:Z1000");
for (var x = 1; x <= range.getWidth(); x++) {
for (var y = 1; y <= range.getHeight(); y++) {
var number = Math.floor(Math.random() * 8999999999) + 1000000000;
range.getCell(y, x).setValue(number);
  }
}

var sheet = SpreadsheetApp.getActive().getSheetByName("sheet1");
var entries = [{
name : "Numbers: 1-9",
functionName : "numbers19"
 }];
 sheet.addMenu("Fill random", entries);
}

This gives a similar output:

8767654543

But when I combine these two codes I continuously receive error messages. I'm simply trying to write a function within a function, but how can I do this with the two different codes? I have tried the following, but it did not work:

SpreadsheetApp.getActiveSheet().getRange('A1:Z10').setValue(61 + var sheet = 
SpreadsheetApp.getActive().getSheetByName("sheet1");
var range = sheet.getRange("A1:Z1000");
for (var x = 1; x <= range.getWidth(); x++) {
for (var y = 1; y <= range.getHeight(); y++) {
var number = Math.floor(Math.random() * 8999999999) + 1000000000;
range.getCell(y, x).setValue(number);
  }
}

var sheet = SpreadsheetApp.getActive().getSheetByName("sheet1");
var entries = [{
name : "Numbers: 1-9",
functionName : "numbers19"
  }];
sheet.addMenu("Fill random", entries);
}
+ "@text.com")

Yes, I have included the function myFunction(){} at the top. What am I missing and how can I fix it?

NOTE 3: I recently attempted this code:

function myFunction() {

function randnumber() {
var sheet = SpreadsheetApp.getActive().getSheetByName("sheet1");
var range = sheet.getRange("A1:Z10");
for (var x = 1; x <= range.getWidth(); x++) {
for (var y = 1; y <= range.getHeight(); y++) {
  var number = Math.floor(Math.random() * 8999999999) + 1000000000;
  range.getCell(y, x).setValue(number);
   }
}

var sheet = SpreadsheetApp.getActive().getSheetByName("sheet1");
var entries = [{
name : "Numbers: 1-9",
functionName : "numbers19"
}];

function copyFormulasToValues() { var ss = 
SpreadsheetApp.getActiveSpreadsheet(); 
var sourceSheet = ss.getSheetByName("Sheet1"); 
var destinationSheet = ss.getSheetByName("Sheet1"); 
var range = sourceSheet.getRange(1,1,1,1);
  }
}


SpreadsheetApp.getActiveSheet().getRange('A1:Z10').setValue("61" + 
randnumber + "@text.com")

}

It gives the following output in each cell:

61
function randnumber() {
var sheet = SpreadsheetApp.getActive().getSheetByName("sheet1");
var range = sheet.getRange("A1:Z10");
for (var x = 1; x <= range.getWidth(); x++) {
    for (var y = 1; y <= range.getHeight(); y++) {
        var number = Math.floor(Math.random() * 8999999999) + 1000000000;
        range.getCell(y, x).setValue(number);
    }
 }
var sheet = SpreadsheetApp.getActive().getSheetByName("sheet1");
var entries = [{name:"Numbers: 1-9", functionName:"numbers19"}];
function copyFormulasToValues() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sourceSheet = ss.getSheetByName("Sheet1");
    var destinationSheet = ss.getSheetByName("Sheet1");
    var range = sourceSheet.getRange(1, 1, 1, 1);
    }
 }
 @text.com

It treats the function that I'm calling as if it was in quotes. How can I fix this?


You have asked for the error message, but the code I shared above recently does not give an error message. It runs the function, but then spits out the unwanted output in each cell on the actual spreadsheet. As to what I am doing with it, it is only a small project to mainly figure out concatenating functions with static number/text values.

1

Answer Wiki

Thanks. We'll let you know when a new response is added.
You might have your answer by now, but in case this is still useful or can be useful by someone else in the future, I will try to explain were you were going wrong.
First, you might want to add your custom menu when the spreedsheet is opened, not when executing a specific function, so you would probably be better off puting that code in an “onOpen()” function.

It would look somehting like this:

function onOpen() {
  var sheet = SpreadsheetApp.getActive();
  var entries = [{
    name : "Numbers: 1-9",
    functionName : "numbers19"
  }];
 
  sheet.addMenu("Fill random", entries);
}
Next, in your first attempt at combining your 2 functions, you were just copying the code of the second function in the place where you wanted its result concatenated.  As you probably guessed, that will not work.
In your second attempt you called the second function from the first one, which makes a little more sense, but doesn’t work as expected either, because your “randnumber” function doesn’t return a single value, but affects the contents of a range of cells without actually returning  anything.

You got the code of the function in every cell because you didn’t add the parentheses() after the function name when calling it.  Had you done that that would have not happened, but it wouldn’t produce the desired results either.

So, to get the results you appear to want, you only need one function, which concatenates “61” to the random number and then to “@text.com” on each cell in the desired range.
So, just making a small modification to one of your functions, it would look like this:
function numbers19() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("sheet1");
  var range = sheet.getRange("A1:Z10");
  for (var x = 1; x <= range.getWidth(); x++) {
    for (var y = 1; y <= range.getHeight(); y++) {
      var number = Math.floor(Math.random() * 8999999999) + 1000000000;
      range.getCell(y, x).setValue("61" + number + "@text.com");
    }
  }
}
I named it “numbers19” because I assumed that you want to execute it when people click on your custom menu option, and that is how you defined it there.
This can most likely be done more efficiently, but this should at least clarify what was wrong in your attempts.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    ...when I combine these two codes I continuously receive error messages.

    What error messages? It'd be easier if we knew what was being reported to you.
    35,650 pointsBadges:
    report
  • ToddN2000
    I agree with Raven. The error message would be a big help. Also, by the samples you gave it looks like you are trying to create a random email address, is that correct? If so for what purpose?

    133,675 pointsBadges:
    report
  • Subhendu Sen

    First of all keep in mind, custom functions can call certain apps script services to perform more complex tasks. Please check this link, https://support.google.com/docs/answer/3094123?hl=en 

    specially follow the Notes section.

    140,480 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: