How to apply a macro folder-wide with google sheets?

Question

This is my first time asking a question so forgive me if I break any rules.

I am using Google Sheets and I want to apply a formatting macro to each spreadsheet in a google drive folder. After looking at previous questions, it appears google does not make this easy and required the user to copy-paste the script into each file to run the macro. I was hoping to run a script using Google Apps Script Drive Service to iterate through a list of files from a folder and then run the function for the macro. I just discovered google apps script last week and could use some help with this task.

The example code begins with some drive service code that is supposed to perform the iteration. Following is a custom function I copied from the macro and named blueHeadings, it changes specific cells blue. I could really use some guidance from a code master.

Furthermore, here is the question that I lifted part of my code from: Google Sheet – using scripts across multiple files without creating a new script in every file

var myFolder = DriveApp.getFoldersByName('Script Testing Folder').next(); //Add Folder name
var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet"); 
while (spreadSheets.hasNext()) // Loops through all Spread Sheets inside the folder.
  {
    var sheet = spreadSheets.next();
    var name = sheet.getName();
    var spreadSheet = SpreadsheetApp.openById(sheet.getId());
    
    function blueHeadings() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1:C1').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
  spreadsheet.getRange('A1:C1').activate();
  spreadsheet.getActiveRangeList().setBackground('#cfe2f3');
  spreadsheet.getRange('A17:C17').activate();
  spreadsheet.getActiveRangeList().setBackground('#cfe2f3');
  spreadsheet.getRange('A20').activate();
  spreadsheet.getActiveRangeList().setBackground('#cfe2f3');
  spreadsheet.getRange('A12').activate();
  spreadsheet.getActiveRangeList().setBorder(false, false, false, false, false, false)
  .setBorder(false, false, false, false, false, false)
  .setBorder(true, null, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  spreadsheet.getRange('B12').activate();
  spreadsheet.getActiveRangeList().setBorder(false, false, false, false, false, false)
  .setBorder(true, null, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  spreadsheet.getRange('C12').activate();
  spreadsheet.getActiveRangeList().setBorder(false, false, false, false, false, false)
  .setBorder(true, null, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  spreadsheet.getRange('B14').activate();
    }
      
  };

MerryLegs MerryLegs · Accepted Answer · 2020-08-29T18:58:22

You don't mention if you've got this script as a standalone script or attached to a specific spreadsheet. If your script is attached to a specific spreadsheet, then that is likely the problem. None of the details you provided require this script to be attached to a spreadsheet.

Thus, my example uses a standalone script to format all spreadsheets in one folder. You can create a standalone script by following these instructions. Paste the following in the standalone script file and run from there.

function blueHeadings() {
var myFolder = DriveApp.getFoldersByName('Script Testing Folder').next(); //Add Folder name
var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet"); 

while (spreadSheets.hasNext()) // Loops through all Spread Sheets inside the folder.
  {
    var sheet = spreadSheets.next();
    var spreadSheet = SpreadsheetApp.openById(sheet.getId());
    
  spreadSheet.getRange('A1:C1').setBackground('#cfe2f3');
  spreadSheet.getRange('A17:C17').setBackground('#cfe2f3');
  spreadSheet.getRange('A20').setBackground('#cfe2f3');
  spreadSheet.getRange('A12').setBorder(true, null, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  spreadSheet.getRange('B12').setBorder(true, null, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);  
  spreadSheet.getRange('C12').setBorder(true, null, null, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);    

 
    }      

}

How to apply a macro folder-wide with google sheets?

1 Answers