How to update multiple google sheet files on google drive from a template without changing the current values of certain cells

Question

I created a google sheet template and then we created almost 200 sheets from that template, now we updated the original template, and I am trying to figure out a way to updated the 200 files without losing the data.

What I did so far is create the script below to create a new file from updated template with the same filename and save it to the same folder as the old file but it fails to delete the old file, I get an error saying:

Exception: Access denied: DriveApp

I have to note the files are created by different users, so if that is the reason of the access denial, is there's a way around it??

Any idea how to solve this??

function createNewAndCopy() {

var ss = SpreadsheetApp.openById("template ID");
var sss = SpreadsheetApp.getActiveSpreadsheet();
var newSS = ss.copy(sss.getName());
// Move to original folder

var originalFolder = DriveApp.getFileById(sss.getId()).getParents().next();
var newSSFile = DriveApp.getFileById(newSS.getId());
originalFolder.addFile(newSSFile);
DriveApp.getRootFolder().removeFile(newSSFile);

var sheetmed = SpreadsheetApp.getActive().getSheetByName('Start here');
var sheet = SpreadsheetApp.openById(newSS.getId()).getSheetByName('Start here');

var range = sheetmed.getRange("A6:D55");
  values = range.getValues();

var ssRange = sheet.getRange("A6:D55");
ssRange.setValues(values);

var range2 = sheetmed.getRange("B1:L2");
  values2 = range2.getValues();

var ssRange2 = sheet.getRange("B1:L2");
ssRange2.setValues(values2);

var ssss = SpreadsheetApp.getActiveSpreadsheet();
//DriveApp.getFilesByName(ssss.getName()).setTrashed(true);
DriveApp.getFileById(ssss.getId()).setTrashed(true);        

}

I'd really appreciate the help

Answer 1

The answer to your question integrates two components.

#1 List the files in your folder

I recall that you said that both the Template as well as the 200 files were in the same folder. So an important aspect is the ability to list and add a formula to add each file excluding the template.

---

// create a list of files on sheet 1 of the linked spreadsheet
// excluded the template
function findFilesInfo() {

  const folderId = '1fZavvilJOOFiD6-FjyTzrg51q9yJIInR'
  const folder = DriveApp.getFolderById(folderId)
  const files = folder.getFiles()
  const source = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = source.getSheetByName('Sheet1');
  const data = [];
  
  const templateID = '1ocHuzWaYJHxPOPZ-p3eXXq-bdpEwolwDYPF1H7gBNsA'


  while (files.hasNext()) {
      const childFile = files.next();
      if (childFile.getId()===templateID){
        //Logger.log("DEBUG: id:"+childFile.getId()+", called "+childFile.getName()+", is the template")
      }
      else{
        Logger.log("id:"+childFile.getId()+" is not the template")
        var info = [ 
          childFile.getName(), 
          childFile.getUrl(),
          childFile.getLastUpdated(),
          childFile.getId() 
        ];
    
      data.push(info);
      }

  }
  sheet.getRange(2,1,data.length,data[0].length).setValues(data);
}

---

File list output

---

#2 Loop through the files and set the formula

// use the list created by findFilesInfo to set formula

function getfiles() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("Sheet1")
  var data = sheet.getDataRange().getValues()

// start at 1 to ignore the header row
  for (var i=1;i<data.length;i++){

    var ssbyid = SpreadsheetApp.openById(data[i][3]);
    var sheetbyid = ssbyid.getSheetByName("Sheet1")
    var cell = sheetbyid.getRange("B1");
    cell.setFormula("=SUM(A1:A2)"); 
    // Logger.log("DEBUG: i:"+i+" Name:"+data[i][0]+"ID:"+data[i][3]+" Set the formula")
  }
  
}

---

Spreadsheet-Before

Spreadsheet-After