跟踪从Google电子表格制作的副本数 [英] Track number of copies made from a google spreadsheet

问题描述

我有一个供用户复制的工作表模板.我想跟踪已经制作了多少份.如果我能真正获得所制作的每个副本的链接，那就太好了.

I have a sheet template that users will be making copies of. I want to track how many copies have been made. It would be great if I can actually get a link to every copy that is made.

有什么办法可以通过应用脚本来做到这一点?

Is there any way to do this via App Scripts?

推荐答案

我一直使用下面代码的修改版本来始终制作和跟踪副本.

I use a modified version of the code below to make and track copies all the time.

有一个菜单项，他们可以用来复制电子表格.该代码会将新电子表格的URL粘贴到您自己的跟踪表中.虽然，即使您告诉他们，也无法保证他们会使用该方法.用户几乎永远不会按照您想要的方式进行操作.

There's menu item they can use to copy the spreadsheet. That code pastes the new spreadsheet's URL in your own tracking sheet. Although, there's no guarantee that they'll use that method even if you tell them to. Users almost never do it the way you want.

使用工作表中的公式计算URL的数量以获取副本的数量.

Use a formula in the sheet to count the number of URLs to get the number of copies.

为使下面的代码起作用，您将需要为 open 函数触发的 On open 安装触发器.用户必须在首次使用脚本时授予该脚本访问权限.

For the code below to work, you will need to install a trigger for the open function triggered On open. The user will have to grant the script access the first time they use it.

如果您想完全控制所有工作表，还可以包括所有权变更.

You could also include a change of ownership if you wanted full control over all sheets.

function open() {

    SpreadsheetApp
        .getUi()
        .createMenu('Your-menu-name')
        .addItem('Make a copy', 'copy')
        .addToUi();
}

function copy() {

    var tracker = SpreadsheetApp.openById('tracker-id-here');
    var template = DriveApp.getFileById('template-id-here');

    var folder = DriveApp.getRootFolder();
    //their root folder

    //var folder = DriveApp.getFolderById('folder-id');
    //use the above line instead if you want to create them in specific folders

    var filename = template.getName();

    var newFile = template.makeCopy(filename, folder);

    var url = newFile.getUrl();

    //newFile.setOwner('email-address-here');
    //use the line above if you want to set an owner who is not the user

    tracker.getSheetByName('your-sheet-name').appendRow([filename, url]);
}

这篇关于跟踪从Google电子表格制作的副本数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！