0

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

3
  • Short answer yes. If you need further help please show what you tried and a add a brief description of your search efforts as is suggested in How to Ask. Feb 9, 2022 at 0:08
  • Hi. Hint: read up on/research setFormula(formula) Doc ref
    – Tedinoz
    Feb 9, 2022 at 6:36
  • I updated the question with more information about what I did, but I still don't have a way to make a script to change the files, thanks for your help
    – Amr
    Feb 9, 2022 at 17:41

1 Answer 1

0

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

file list


#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

Before

Spreadsheet-After

After

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.