5

I want to copy an entire spreadsheet of some 20+ sheets to a different location in Drive; however, I only want to preserve the hard values and formatting in each cell, and not the formulas (basically just taking a snapshot of the values). I have been playing around with how to write this but I don't have a solid idea on what's the best approach. I'm just starting to learn conditionals like looping in my google sheets training, any help would be appreciated. Thanks!

enter image description here

Those cells in green are all vlookups and they update from an array I have on another spreadsheet. The idea is to get all the right data in the array, have this sheet fully fill out with the correct values for the day, then save it preferably as a google sheet but just the values, so that they are editable after the fact if there was an error in the array data.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Warren Meroney
  • 195
  • 2
  • 10
  • Can you provide the sample Spreadsheet? For example, in your Spreadsheet, the formulas which are required to be authorized are used? Because, the script is required to be changed by the formulas which are used in the Spreadsheet. And also, if you have the script, please provide it. Of course, please remove your personal information. – Tanaike Nov 18 '19 at 22:28
  • seems like a waste to copy everything with a google app script, but if you need to for archive purposes just export as a pdf in my answer. – CodeCamper Nov 18 '19 at 23:01
  • Well the spreadsheet in question has many vlookups and other outside reference cells in it, and it updates on a daily basis, so I would like a way to preserve all the values at the moment in a facsimile of the spreadsheet so that edits could be made after the "copying" each sheet. – Warren Meroney Nov 18 '19 at 23:24
  • Thank you for replying and adding the information. Can I ask you about your goal? You want to copy the source Google Spreadsheet as the Google Spreadsheet? Or you want to copy it as other format? – Tanaike Nov 18 '19 at 23:36
  • Basically I just want to have all the raw data on each sheet of this spreadsheet photocopied for lack of a better word and just archived somewhere else in drive with a date stamped on it for a name. I do not want the functions for the respective cells to carry over in case an edit after the fact has to be made, I would rather they just add or subtract from the cell end value that was copied. – Warren Meroney Nov 19 '19 at 00:38
  • Thank you for replying. From your replying and the additional information, I proposed a sample Spreadsheet. Could you please confirm it? If that was not the direction you want, I apologize. At that time, can you provide a sample Spreadsheet for replicating the issue? By this I would like to modify it. Of course, please remove your personal information. – Tanaike Nov 19 '19 at 01:14

3 Answers3

5
  • You want to copy the source Spreadsheet in the specific folder in your Google Drive.
  • You want to use a date stamped on it for a name as the filename.
  • You don't want to copy the formulas. You want to copy only displaying values.
  • In your Spreadsheet, the formulas of many vlookups and other outside reference cells are included.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

The flow of the sample script for the above goal is as follows.

Flow:

  1. Copy all sheets in the source Spreadsheet as the temporal sheets.
  2. At the copied sheets, the cells are overwritten by only the values. By this, the formulas can be removed.
  3. Copy the source Spreadsheet.
  4. Delete the temporal sheets in the source Spreadsheet.
  5. Delete the original sheets in the destination Spreadsheet.
  6. Move the copied Spreadsheet to the specific folder.

Sample script:

Before you run the script, please set the source Spreadsheet ID and the destination folder ID.

function myFunction() {
  var spreadsheetId = "###"; // Please set the source Spreadsheet ID.
  var destFolderId = "###";  // Please set the destination folder ID.

  // Copy each sheet in the source Spreadsheet by removing the formulas as the temporal sheets.
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var tempSheets = ss.getSheets().map(function(sheet) {
    var dstSheet = sheet.copyTo(ss).setName(sheet.getSheetName() + "_temp");
    var src = dstSheet.getDataRange();
    src.copyTo(src, {contentsOnly: true});
    return dstSheet;
  });
  
  // Copy the source Spreadsheet.
  var destination = ss.copy(ss.getName() + " - " + new Date().toLocaleString());
  
  // Delete the temporal sheets in the source Spreadsheet.
  tempSheets.forEach(function(sheet) {ss.deleteSheet(sheet)});
  
  // Delete the original sheets from the copied Spreadsheet and rename the copied sheets.
  destination.getSheets().forEach(function(sheet) {
    var sheetName = sheet.getSheetName();
    if (sheetName.indexOf("_temp") == -1) {
      destination.deleteSheet(sheet);
    } else {
      sheet.setName(sheetName.slice(0, -5));
    }
  });

  // Move file to the destination folder.
  var file = DriveApp.getFileById(destination.getId());
  DriveApp.getFolderById(destFolderId).addFile(file);
  file.getParents().next().removeFile(file);
}

Note:

  • In this sample script, the script of this answer was used.
  • This is a simple sample script. So please modify this for your actual situation.

References:

ChimdumebiNebolisa
  • 335
  • 2
  • 11
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • That is exactly what I wanted Tanaike, thank you so much! Saved me a bunch of time. Please let me tip you :) – Warren Meroney Nov 19 '19 at 17:49
  • @Warren Meroney Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too. – Tanaike Nov 19 '19 at 22:48
  • @Tanaike great answer, could you please explain what the 'i' is for in 'ss.getSheets().map(function(sheet, i)' I thought getSheets only returned a single dimensional array? – CodeCamper Nov 19 '19 at 23:29
  • @CodeCamper Thank you for replying. `i` of `ss.getSheets().map(function(sheet, i)` is the index of array which is `ss.getSheets()`. In this case, the 1 dimensional array is returned by `return dstSheet;`. By the way, I had used `i` for debugging. I forgot to remove this. So I deleted it now. – Tanaike Nov 19 '19 at 23:34
  • @Tanaike why do you use `file.getParents().next().removeFile(file);` instead of just removing the file? I am confused by this step. Instead of just `DriveApp.getFolderById(destFolderId).removeFile(file);` – CodeCamper Nov 20 '19 at 01:25
  • @CodeCamper I used `file.getParents().next().removeFile(file);` for using `var file = DriveApp.getFileById(destination.getId());`. By this, the original parent (in this case, it's root.) of `file` is removed. I have to apologize that if this script was low readability. By the way, when `DriveApp.getFolderById(destFolderId).removeFile(file);` is used, isn't `DriveApp.getFolderById(destFolderId).addFile(file);` canceled? If I misunderstood your goal, I apologize. – Tanaike Nov 20 '19 at 01:44
  • @Tanaike You are right, I wasn't aware that google app script / google drive made moving files around so complicated. What confused me is how you can have the same file in multiple places and removing the file is not the same thing as deleting the file. – CodeCamper Nov 20 '19 at 02:12
  • @CodeCamper I think so. In Google Apps Script, when a parent is added and removed from a file, the parent is used as Class. Then, the parent adds the file and/or the parent removes the file. It has taken a little time for me to get used to it. – Tanaike Nov 20 '19 at 02:37
  • Thanks again you guys for helping me get through this problem. I have one last question Tanaike about your script, is there a [relatively] easy way may to modify it to preserve the formulas of certain cells? Not that big of a deal, but just curious if that is easily done or not. Again, thank you so much for this! – Warren Meroney Nov 20 '19 at 18:28
  • @Warren Meroney Thank you for replying. Although I'm not sure whether I could correctly understand about your new question, when you don't want to delete the formulas, how about removing `var src = dstSheet.getDataRange(); src.copyTo(src, {contentsOnly: true});` from my script? But I cannot understand about your actual situation. So if this doesn't resolve your new question, can you post it as new question by including the detail information? By this, it will help users including me think of the issue and solution. If you can cooperate to resolve your issue, I'm glad. – Tanaike Nov 20 '19 at 22:40
  • 1
    @Tanaike I will do so Tanaike! :) – Warren Meroney Nov 22 '19 at 00:14
0

If you actually plan on using this data you probably want to think twice of just randomly copying everything. If you want to just archive Just add export?format=zip to the end of your link, then you will have a saved copy with values and formatting.

CodeCamper
  • 6,609
  • 6
  • 44
  • 94
  • Thank you Camp for the suggestion, this is the route I was originally thinking about taking but my boss insisted on it being on sheets > – Warren Meroney Nov 19 '19 at 19:18
  • @WarrenMeroney Well if you want it to be archived for record keeping purposes saving it as a sheet is a terrible idea because you can easily accidentally edit your records or backups. If you want to somehow extract the data for later use then it makes sense to do it with sheets. – CodeCamper Nov 19 '19 at 22:56
  • Yes correct, that was his intention is to point it towards another sheet in the long run :) – Warren Meroney Nov 20 '19 at 17:58
0

Another very simple way is to Download the sheet to Excel. It will keep local formulas but remove external references (like importrange, etc), so data will stay frozen ;) You can even open the Excel file from Gsheets (without converting it to sheets).

rbravoz
  • 1