我需要使用脚本将Google电子表格的副本保存到特定目录 [英] I need to save a copy of a google spreadsheet to a specific directory using script

问题描述

我们当前有一个每晚平衡的过程，其中我们打开一个Excel模板电子表格，输入正确的处理日期，然后单击一个按钮，从而触发VB脚本并使用适当的名称创建电子表格的新副本(例如"BAL 080114")，然后将其打开，然后操作员关闭模板，并继续在新副本中进行工作.

We currently have a nightly balancing process wherein we open an Excel template spreadsheet, enter the correct processing date, and then click a button, causing a VB script to fire off and create a new copy of the spreadsheet with the appropriate name (eg, "BAL 080114") in the appropriate folder and opens it, at which point the operator then closes the template, and continues work in the new copy.

文件夹结构为:

Drive
--->Ops Room
------->Procedural Sheets
----------->Night Shift
--------------->Balancing
------------------->2014
----------------------->01-2014
...
----------------------->12-2014

我们正在尝试将其转换为Google文档电子表格，并且大多数情况下都可以使用.但是我找不到允许某人打开模板(存储在Balancing中)，运行开始新的一天"脚本以及让该脚本在适当的子文件夹中创建文件的方法.例如，对于2014年8月1日，该文件应以Bal 080114的形式存储在Balancing/2014/08-2014中.

We are trying to transition this to Google docs spreadsheets, and mostly it's working. But I cannot find a method to allow someone to open the template (stored in Balancing), run a "Start New Day" script, and have the script create the file in the proper sub-sub-folder. To wit, for 08/01/2014, the file should be stored in Balancing/2014/08-2014 as Bal 080114.

这是我到目前为止的内容:

This is what I have thus far:

function startNewDay() {
  // This code makes a copy of the current spreadsheet and names it appropriately
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // The file name is created and stored on sheet "Set Date" in cell B5
  var fname = ss.getSheetByName("Set Date").getRange("B5").getValue();
  var folderYear = Utilities.formatDate(new Date(), "GMT-6", "yyyy"); // top-level folder is by year in yyyy format
  var folderMonth = Utilities.formatDate(new Date(), "GMT-6", "MM-yyyy"); // folder name is in mm-yyyy format
  //the above is probably overkill, but I'll work on efficiency once I get it working at all :
  //Everything works up to this point...

  //This is where I start running into problems...
  //The Master Copy SSID is <redacted>
  var SSID = '<redacted>'
  var folder = DocsList.getFolder(folderYear + "/" + folderMonth); 
  var backup = DocsList.getFileById(SSID).makeCopy(fname);
  backup.addToFolder(folder); //This line will move the file to the appropriate folder
  backup.removeFromFolder(DocsList.getRootFolder()); //This line is needed to remove the File from the Root
}

我从另一个具有类似属性的StackOverFlow答案中借来了备份.*的东西，但是我的版本没有创建该文件.

I borrowed the backup.* stuff from another StackOverFlow answer that had similar properties, but my version doesn't create the file.

我正在云端硬盘中尝试做的事情吗?还是只需要让操作员创建一个副本然后手动移动它即可?

Is what I'm trying to do even possible in Drive? Or will I just need to have the operators create a copy and then move it manually?

对于任何脚本的无知我深表歉意-我本周刚开始学习Google脚本，并且在与以前的VB经验中找不到共同点时遇到了困难.

I apologize for any scripting ignorance - I've just started learning Google script this week, and I'm having trouble finding a common ground with my previous VB experience.

在此先感谢您的帮助.

詹姆斯

推荐答案

我无法发表评论，因此无法添加到已经存在的内容中.

I am not able to comment so I cannot add on to what is already there.

是否由正确的人使用正确的权限创建手动"创建的文件夹?共享文件夹在Google云端硬盘上可能会很棘手.

Are the folders that are "manually" being created being created with the correct permissions by the correct person? Shared Folders can get tricky on Google Drive.

我什至可以建议这样的事情，并让脚本为您创建文件夹:

I may even suggest something like this and have the script just create the folders for you:

/*********************************************************
 * Function to determine the destination folder based on
 *   provided criteria.
 *
 * @param {Folder} root The root folder.
 * @param {String} folderYear The year of the report.
 * @param {String} folderMonth The month of the report.
 * @return {Folder} Destination Folder.
 *********************************************************/
function returnFolder(root, folderYear, folderMonth) {
  var dir      = DocsList.getFolderById(root);
  var folders  = DocsList.getFolderById(root).getFolders();
  var found    = false;
  var toReturn = "";

  for (var i = 0; i < folders.length; i++) {
    if (folders[i].getName() == folderYear) {
      dir      = folders[i];
      found    = true;
      break;
    }
  }

  if (!found) {
    dir = dir.createFolder(folderYear);
    folders = dir.getFolders();
  }
  else found = false;

  for (var i = 0; i < folders.length; i++)
    if (folders[i].getName() == folderMonth) {
      toReturn = folders[i].getId();
      found    = true;
      break;
    }

  if (!found) toReturn = dir.createFolder(folderMonth).getId();
  return DocsList.getFolderById(toReturn);
}

然后您的代码如下:

var SSID = '<redacted>'
var folder = returnFolder(OBJECT_PARENT_FOLDER, folderYear, folderMonth);
var backup = DocsList.getFileById(SSID).makeCopy(fname);
backup.addToFolder(folder); //This line will move the file to the appropriate folder
backup.removeFromFolder(DocsList.getRootFolder()); //This line is needed to remove the File from the Root

您需要将代码与您的需求相匹配，但是我希望这会有所帮助.

You will need to match the code to your needs, but I hope this helps.

这篇关于我需要使用脚本将Google电子表格的副本保存到特定目录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！