CODEWITHSUNDEEP
javascriptgoogle-apps-scriptstaticformula
Anomalous
Anomalous

Reputation: 65

How do I Set a formula to value in Google scripts?

I have written a code that generates random numbers and concatenates them with text. I am using this function to fill a range of cells (from A1 to Z10):

function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var cell = sheet.getRange("A1:Z10");
cell.setFormula('=CONCATENATE("61", RANDBETWEEN(1000000000, 9999999999), "@text")');
}

The problem with this is that every time I reload the page, the numbers are recalculated, there is no static value. I want the values in my range to become static, so that every time I reload the page, the numbers in between the "61" and "@text" remain the same. I have tried the following two codes, and neither has worked. The first did not receive an error message, but simply did not keep the values static, while the second received the error message ""

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

The second code:

var studentSheet = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = SpreadsheetApp.getActiveSpreadsheet();

// copyTo rows
sourceSheet.getRange(1, 1, 10, 26).copyTo(studentSheet.getRange(1, 1, 10, 26), {contentsOnly: 
true});

SpreadsheetApp.flush(); // applies all values to the student spreadsheet
                    // and therefore all the formula cells update
// get value from calculated formula cell
studentSheet.getRange(1, 1, 10, 26).copyTo(sourceSheet.getRange(1, 1, 10, 26), {contentsOnly: 
true});
}

The error message:

Cannot find method getRange(number,number,number,number). (line 11, file "Code")

I just want to copy and paste the values in the same spreadsheet so they remain static. How can I achieve this?

NOTE 1.

I really appreciate the help Tanaike. I tried out the script (which I have posted below), but when I try and run it, the function runs the code, and then just entirely deletes the values.

function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var cell = sheet.getRange("A1:Z10");
cell.setFormula('=CONCATENATE("61", RANDBETWEEN(1000000000, 9999999999), 
"@text.com")');

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
var range = sheet.getRange("A1:Z10");
range.copyValuesToRange(sheet, 1, range.getNumColumns(), 1, 
range.getNumRows());
}

Perhaps I am doing something wrong?

Upvotes: 2

Views: 113

Answers (1)

Tanaike
Tanaike

Reputation: 201338

  • You want to remove the formulas and put the values to the cells of A1:Z10 which is the same range.
  • The formulas has already been put to the Spreadsheet.

If my understanding is correct, how about this answer?

Pattern 1:

In this pattern, copyValuesToRange is used.

Sample script:

function copyFormulasToValues() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var range = sheet.getRange("A1:Z10");
  range.copyValuesToRange(sheet, 1, range.getNumColumns(), 1, range.getNumRows());
}
  • I think that in your script, the arguments might be wrong. Please set sheet, column, columnEnd, row, rowEnd in order.

Pattern 2:

In this pattern, copyTo is used.

Sample script:

function copyFormulasToValues() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getSheetByName("Sheet1").getRange("A1:Z10");
  range.copyTo(range, {contentsOnly: true});
}

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 1

Related Questions