CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-apigoogle-sheets-vlookup
Warren Thornton
Warren Thornton

Reputation: 305

Copy an entire Spreadsheet, Just Preserve the Values

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.

Upvotes: 5

Views: 4357

Answers (3)

Tanaike
Tanaike

Reputation: 201408

  • 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:

Upvotes: 5

rbravoz
rbravoz

Reputation: 1

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).

Upvotes: 0

CodeCamper
CodeCamper

Reputation: 6980

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.

Upvotes: 0

Related Questions