Using scripts across multiple files without creating a new script in every file

Question

Google Sheet lacks some basic functions that Excel has and I need.

E.g. Indent text - I downloaded a script off Github, used the script editor to run a script that can indent text successfully.

The code can be found here:

var ss = SpreadsheetApp.getActiveSpreadsheet();

function moveText(direction) {
  var values = ss.getActiveRange().getValues();
  var cols = ss.getActiveRange().getNumColumns();
  var rows = ss.getActiveRange().getNumRows();

  var newValues = new Array();

  for (x = 1; x <= rows; x++) {
    for (y = 1; y <= cols; y++) {
      var cell = ss.getActiveRange().getCell(x, y);
      var value = cell.getValue();
      var formula = (direction == ">>>") ? '=CONCAT(REPT( CHAR( 160 ), 5),"' + value + '")'
      : '=IF(TRIM(LEFT("' + value + '", 5))=CONCAT(REPT( CHAR( 160 ), 5),""), MID("' + value + '", 6, LEN("' + value + '")), TRIM("' + value + '"))';

      if (value != '') {
        cell.setFormula([formula]);
        cell.setValue(cell.getValue());
      } else {
        cell.setValue(['']);
      }
    }
  }
};

function indentText() {
  moveText(">>>");
};

function flushLeft() {
  moveText("<<<");

};

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var entries = [{
    name : ">>>",
    functionName : "indentText"
  },{
    name : "<<<",
    functionName : "flushLeft"

  }];
  sheet.addMenu("Indent Text", entries);
};

The script works perfectly in the file where it is housed.

I would like to use this script in every Google Sheet file (through a master list of Google scripts associated with my account) without having to paste the code into the script editor every time I create a new file/use an old file (this is because I have multiple Sheets files which are shared with people across my organization).

Answer 1

You can get all the files from drive using DriveApp.

https://developers.google.com/google-ads/scripts/docs/examples/driveapp

var sheetFiles = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
  while (docs.hasNext()) 
  {
    var sheet = sheetFiles.next();
    var name = sheet.getName();
    var spreadSheet = SpreadsheetApp.openById(sheet.getId());

    var ss = spreadSheet.getActiveSheet();
    function moveText(direction) {
      var values = ss.getActiveRange().getValues();
      var cols = ss.getActiveRange().getNumColumns();
      var rows = ss.getActiveRange().getNumRows()
      var newValues = new Array();
      for (x = 1; x <= rows; x++) 
      {
        for (y = 1; y <= cols; y++) 
        {
          var cell = ss.getActiveRange().getCell(x, y);
          var value = cell.getValue();
          var formula = (direction == ">>>") ? '=CONCAT(REPT( CHAR( 160 ), 5),"' + value + '")'
          : '=IF(TRIM(LEFT("' + value + '", 5))=CONCAT(REPT( CHAR( 160 ), 5),""), MID("' + value + '", 6, LEN("' + value + '")), TRIM("' + value + '"))';

          if (value != '') {
            cell.setFormula([formula]);
            cell.setValue(cell.getValue());
          } else {
            cell.setValue(['']);
          }
        }
      }
    };

    function indentText() {
      moveText(">>>");
    };    
    function flushLeft() {
      moveText("<<<");      
    };    
    function onOpen() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet();      
      var entries = [{
        name : ">>>",
        functionName : "indentText"
      },{
        name : "<<<",
        functionName : "flushLeft"

      }];
      sheet.addMenu("Indent Text", entries);
    };  
  }  

Try this one. Hope it works for you.

If the individual sheets are inside a folder you may need to use DriveApp.getFolderById() too.

https://developers.google.com/apps-script/reference/drive/drive-app

https://developers.google.com/apps-script/reference/base/mime-type

Edited Answer :

Select all the google sheets you want the script to work with and place it in a folder.

Drive -> (Folder) -> Google sheets

now go to https://script.google.com/home

create New Script

add the below code.

var myFolder = DriveApp.getFoldersByName('Folder name').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());

    var ss = spreadSheet.getActiveSheet();
    function moveText(direction) {
      var values = ss.getActiveRange().getValues();
      var cols = ss.getActiveRange().getNumColumns();
      var rows = ss.getActiveRange().getNumRows()
      var newValues = new Array();
      for (x = 1; x <= rows; x++) 
      {
        for (y = 1; y <= cols; y++) 
        {
          var cell = ss.getActiveRange().getCell(x, y);
          var value = cell.getValue();
          var formula = (direction == ">>>") ? '=CONCAT(REPT( CHAR( 160 ), 5),"' + value + '")'
          : '=IF(TRIM(LEFT("' + value + '", 5))=CONCAT(REPT( CHAR( 160 ), 5),""), MID("' + value + '", 6, LEN("' + value + '")), TRIM("' + value + '"))';

          if (value != '') {
            cell.setFormula([formula]);
            cell.setValue(cell.getValue());
          } else {
            cell.setValue(['']);
          }
        }
      }
    };

    function indentText() {
      moveText(">>>");
    };    
    function flushLeft() {
      moveText("<<<");      
    };    
    function onOpen() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet();      
      var entries = [{
        name : ">>>",
        functionName : "indentText"
      },{
        name : "<<<",
        functionName : "flushLeft"

      }];
      sheet.addMenu("Indent Text", entries);
    };  

The above code loops through all your wanted spreadsheet and intend text successfully in all sheets, i didnt test it. Try it out and let me know.

i apoligize for not able to explain properly.

i am new to google sheets too, just worked for a day or two for another team. just trying to learn.