How to apply a Google Script across all sheets in a folder

1
votes

I'm a complete newbie to scripts. I have a script (pasteValues) that

  1. Looks at the active spreadsheet
  2. Pastes the values so that all formulas become static values.
  3. Inserts a hyperlink formula into a specific named cell (L3 for this example).
 function onOpen() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var pasteValues = [ {name: "Paste Values", functionName: "pasteValues"}];
  ss.addMenu("Paste Values", pasteValues);
}


     function pasteValues() {
        var spreadsheet = SpreadsheetApp.getActive();
      var currentCell = spreadsheet.getCurrentCell();
      spreadsheet.getActiveRange().getDataRegion().activate();
      currentCell.activateAsCurrentCell();
      currentCell = spreadsheet.getCurrentCell();
      var sheet = spreadsheet.getActiveSheet();
      sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
      currentCell.activateAsCurrentCell();
      sheet = spreadsheet.getActiveSheet();
      sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  
  var cell = sheet.getRange("L3");
cell.setFormula('=HYPERLINK("https://docs.google.com/spreadsheets/d/11wN66ClYQf4soG6UnlWrqBz5hSMSj0c8ceI_0Zt-s7o/","Link to Master Schedule")')
}

;

I currently have installed this script on 100+ different spreadsheets and use a menu option to run the script (I open each spreadsheet and run the script via the menu tab generated in the "onOpen" script).

What I would like to do is be able to

  1. Open my my master sheet (called "Sample Schedule")
  2. Run the pastevalues function script (accessed via a menu tab ideally) across all sheets that are in the same folder as "Sample Schedule"
  3. But the pastevalues function should only be used on a specifically named sheet. (I.e. Each sheet has 10 or more tabs, the tab called "Summer 2021" is the one I want to use the pastevalues function on. This tab name is consistent across all sheets in the folder.

I currently have a script (installed on "Sample Schedule" that I have used for awhile that copies the Sample Schedule's active tab to all sheets in the folder. This is what is currently working to achieve this:

function copySheet() {
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = source.getSheets()[0];
  var sourceFile = DriveApp.getFileById(source.getId());
  var sourceFolder = sourceFile.getParents().next();
  var folderFiles = sourceFolder.getFiles();
  var thisFile;

  while (folderFiles.hasNext()) {
    thisFile = folderFiles.next();
    if (thisFile.getName() !== sourceFile.getName()){
      var currentSS = SpreadsheetApp.openById(thisFile.getId());
      sheet.copyTo(currentSS);
      currentSS.getSheets()[currentSS.getSheets().length-1].setName('Summer 2021').activate();
      currentSS.moveActiveSheet(1);
    }    
  };    
}

But I've failed to adapt this script to my new "PasteValues" script.

I've looked at so many answers on Stack Overflow for how to run a function across multiple sheets in the same folder and I'm just not able to do it (I only just managed to make the paste Values (paste values + a hyperlink in a specific cell work). I've struggled on and off for months trying to achieve this while I have spare time, but if anyone could help me out, it would be wonderful.

1
google-apps-scriptgoogle-sheets

1 Answers

2
votes

I believe your goal as follows.

  • You want to retrieve the Google Spreadsheet files under the specific folder which has the active Spreadsheet.
  • For the specific sheet of Summer 2021 in each Spreadsheet, you want to copy the current values with the display values without the formulas. And, you want to put a formula to the cell "L3".

Modification points:

  • In this case, I thought that at first, it is required to reduce the process cost of your current script. In this case, I modified it as follows.

      var sheet = SpreadsheetApp.getActiveSheet()
  var range = sheet.getDataRange();
  range.copyTo(range, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  sheet.getRange("L3").setFormula('=HYPERLINK("https://docs.google.com/spreadsheets/d/###/","Link to Master Schedule")');

Using above modified script, I modified copySheet() of your script as follows.

Modified script:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var pasteValues = [{ name: "Paste Values", functionName: "pasteValues" }];
  ss.addMenu("Paste Values", pasteValues);
}

function pasteValues() {
  var sheetName = "Summer 2021";

  // 1. Retrieve the parent folder of active Spreadsheet.
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sourceFile = DriveApp.getFileById(source.getId());
  var sourceFolder = sourceFile.getParents().next();
  
  // 2. Retrieve all Google Spreadsheet files under the folder which has the active Spreadsheet.
  var files = sourceFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
  while (files.hasNext()) {
    
    // 3. Run the script you want to run each Spreadsheet.
    var sheet = SpreadsheetApp.open(files.next()).getSheetByName(sheetName);
    if (sheet) {
      var range = sheet.getDataRange();
      range.copyTo(range, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      sheet.getRange("L3").setFormula('=HYPERLINK("https://docs.google.com/spreadsheets/d/11wN66ClYQf4soG6UnlWrqBz5hSMSj0c8ceI_0Zt-s7o/","Link to Master Schedule")');
    }
  }
}

  • When you run pasteValues(), the following flow is run.

    1. Retrieve the parent folder of active Spreadsheet.
    2. Retrieve all Google Spreadsheet files under the folder which has the active Spreadsheet.
    3. Run the script you want to run each Spreadsheet.

Note:

  • About 100+ different spreadsheets in your question, unfortunately, I'm not sure whether above script works for all Spreadsheets in one execution. In my environment, when 10 Spreadsheets are used, the process time was about 25 seconds. From this situation, I think that 100 Spreadsheet files might work. But, when you tested this, when all Spreadsheets cannot be done, I thought that it might be required to use the time-driven trigger.

References: