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.
Discuss This Question: 3 Replies
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.