How to copy the format and values, not formulas, of certain cellsin Google Apps Script?

by

I have a file with quite a few sheets (around 70). I would like to generate an independent file for each sheet whose name corresponds to the name of the sheet. I figured out how to do this part.

Except that I have certain cells that come from a formula and I want to retrieve only the values, not the formulas. I came across this answer:How to copy format and values, not formulas, when creating a spreadsheet backup in Google Apps Script?

The problem is that my script cannot complete because I exceed the maximum execution time (Error: Exceeded maximum execution time).
Is there a solution to get to the end?

function copyEntireSpreadsheet() {
  var id = "###"; // Please set the source Spreadsheet ID.

  var ss = SpreadsheetApp.openById(id);
  var srcSheets = ss.getSheets();
  var tempSheets = srcSheets.map(function(sheet, i) {
    var sheetName = sheet.getSheetName();
    var dstSheet = sheet.copyTo(ss).setName(sheetName + "_temp");
    var src = dstSheet.getDataRange();
    src.copyTo(src, {contentsOnly: true});
    return dstSheet;
  });
  var destination = ss.copy(ss.getName() + " - " + new Date().toLocaleString());
  tempSheets.forEach(function(sheet) {ss.deleteSheet(sheet)});
  var dstSheets = destination.getSheets();
  dstSheets.forEach(function(sheet) {
    var sheetName = sheet.getSheetName();
    if (sheetName.indexOf("_temp") == -1) {
      destination.deleteSheet(sheet);
    } else {
      sheet.setName(sheetName.slice(0, -5));
    }
  });
}

  • 1

    Search in the get Section of the Range Class or use copyTo()

    – 




  • 2

    First, I deeply apologize that my answer was not useful for your situation. About your question, how about using Sheets API instead of Spreadsheet service (SpreadsheetApp)? But, I cannot know your actual situation including the script and spreadsheet. So, I cannot propose a sample script for directly testing your situation, yet. I apologize for this situation.

    – 

  • Would you please edit the question to include the script.

    – 

  • @Tanaike i don’t unterstand your comment.

    – 

  • @Tedinoz question edited

    – 

Try creating your backups this way

It creates a stringified JSON version of every sheet similar to the files you can retrieve via Apps Script API and it does it fast. You can go into the file with a text editor and copy the stringified data and paste into a textbox in a side bar and with a simple code that parses it you can use setValues method to return it to the sheet. Of course the other possibility is that you could just retrieve the current version from that google has already saved for you.

function copyEntireSpreadsheet() {
  const id = gobj.globals.test1id;
  const dfldr = DriveApp.getFolderById(gobj.globals.folder1id);
  const ss = SpreadsheetApp.openById(id);
  const shts = ss.getSheets();
  const obj = shts.reduce((a,sh,i) => {
    let n = sh.getName();
    a[n] = sh.getDataRange().getValues();
    a.pA.push(n)
    return a;
  },{"SpreadsheetName":ss.getName(),pA:[]});
  const ds = Utilities.formatDate(new Date(),ss.getSpreadsheetTimeZone(),"yyyyMMdd HHmmss");
  dfldr.createFile(`${obj.SpreadsheetName}-${ds}`,JSON.stringify(obj),MimeType.PLAIN_TEXT);
}

Leave a Comment