0

I have a master Google Sheet with formulas. The user enters some details and the script then makes a copy of the sheet using the copy method:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var tmpSS = ss.copy(bookID + "-" + title);

When I open the new spreadsheet, there are only values in the cells, the formulas have not been copied over. How do I copy over the formulas as well?

Tomerikoo
  • 18,379
  • 16
  • 47
  • 61
Kevin Bird
  • 1
  • 1

3 Answers3

0

Try using this :

    var sheetId = SpreadsheetApp.getActiveSpreadsheet().getSheetId();

    DriveApp.getFileById(sheetId).makeCopy("SheetName");
Suyash Gandhi
  • 926
  • 6
  • 24
-1

As mentioned here: GAS-copyto, the method copyTo on sheet should make a copy of the sheet with values and formulae. So maybe you could write a loop to create copies of all sheets of a given spreadsheet, using this.

Community
  • 1
  • 1
Sujay Phadke
  • 2,145
  • 1
  • 22
  • 41
  • The problem is because he is expecting to copy a formula with a function that only copies actual data and formatting – Vytautas Jan 31 '17 at 11:50
  • @Vytautas That's incorrect. copyTo does copy formulae – Sujay Phadke Jan 31 '17 at 13:19
  • I retract my statement, a test shows that indeed it can copy formula. My answer still stands, using `.getFormulas()` and `.setFormulas()` will work – Vytautas Jan 31 '17 at 13:37
  • Thanks for the replies but I must be missing something. I used the following code and the newly created spreadsheet only shows values, not formulas and values. var ss = SpreadsheetApp.getActiveSpreadsheet(); sheet = ss.getActiveSheet(); var tmpSS = SpreadsheetApp.create(bookID + "-" + title); sheet.copyTo(tmpSS); do you know what I am missing? – Kevin Bird Jan 31 '17 at 14:03
-1

As per the documentation you can use .getFormulas() and .setFormulas() to copy over formulas. You can copy to any sheet or spreadsheet you have access to.

Vytautas
  • 2,238
  • 1
  • 9
  • 20
  • 1
    While this might be a valuable hint to solve the problem, a good answer also demonstrates the solution. Please [edit] to provide example code to show what you mean. Alternatively, consider writing this as a comment instead. – Toby Speight Jul 26 '17 at 12:13