在多个文件中使用脚本，而无需在每个文件中创建新脚本 [英] Using scripts across multiple files without creating a new script in every file

问题描述

Google表格缺少Excel具有的一些基本功能，而我需要这些功能.

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

例如缩进文本-我从Github下载了一个脚本，使用脚本编辑器运行了可以成功缩进文本的脚本.

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

可以在此处:

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.

我想在每个Google表格文件中使用此脚本(通过与我的帐户关联的Google脚本的主列表)，而不必在每次创建新文件/使用旧文件时将代码粘贴到脚本编辑器中(这是因为我有多个Sheets文件，这些文件与我组织中的所有人共享).

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).

推荐答案

您可以使用DriveApp从驱动器中获取所有文件.

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.

如果各个工作表都在文件夹中，则可能还需要使用DriveApp.getFolderById().

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

已编辑答案:

选择您要使用脚本的所有Google工作表并将其放置在文件夹中.

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

驱动器->(文件夹)-> Google表格

Drive -> (Folder) -> Google sheets

现在转到 https://script.google.com/home

创建新脚本

添加以下代码.

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.

我因无法正确解释而poli之以鼻.

i apoligize for not able to explain properly.

我也是Google表格的新手，刚为另一支团队工作了一两天. 只是想学习.

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

这篇关于在多个文件中使用脚本，而无需在每个文件中创建新脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！