我如何创建一个新的gsheet与脚本已经在它
How do i create a new gsheet with a script already in it?
我有一个系统,我自动上传文件到Gdrive, Gdrive搜索这些文件,合并它们,并在合并的表中添加模板表。
在模板gsheet中,我需要能够在不使用选项卡名称的情况下引用其他选项卡内的单元格(选项卡名称将一直在变化)。我知道如何做到这一点的唯一方法是使用gsheet内的谷歌应用程序脚本。问题是我生成模板的方式是通过打开模板并将所有值复制到新创建的组合gsheet中的新选项卡,它不会复制谷歌应用程序脚本。
即使我可以使用代码自动向新工作表添加脚本,我仍然需要该脚本在该工作表上自动运行,因为我有另一个母表查找所有新创建的文件并添加所有这些文件的摘要信息。
如果有帮助的话,这是我的Gdrive脚本。
/* Head Master Info >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */
/* Top Level Google Drive Folder ID */
var TopDriveFolderID = "0B2rN5b8fW77ldXZXOXFLZGlSamc";
var BaseNumberOfFilesInFolder = 4;
var NewSSAnalyzerSheetId = "1VyL8XL1x41H6yetcrwfUhVcch-DUsngY4-bHCbo-_xM";
/* End Head Master Info >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/* Body >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/* Functions to iterate through all sub folders */
function listFolders() {
var parentFolder = DriveApp.getFolderById(TopDriveFolderID);
var childFolders = parentFolder.getFolders();
while(childFolders.hasNext()) {
var child = childFolders.next();
// Logger.log(child.getName() + " |Drive_ID: " + child.getId());
var folderId = child.getId();
// **the folderId Variable is also the folder ID,
// hence they are used interchangeably **
/* SpreadSheet Combiner >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/* Name of combined Spreadsheet*/
var newSpreadSheetName = DriveApp.getFolderById(folderId).getName();
/* Retrieve the desired folder */
var myFolder = DriveApp.getFolderById(folderId);
/* Get all spreadsheets that resided on that folder */
var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");
var spreadSheetName = myFolder.getName();
// Logger.log(spreadSheetName);
/* Check if a file needs to be created or if one already exists*/
var theFolder = DriveApp.getFolderById(folderId);
var files = theFolder.getFiles();
var fname = theFolder.getName();
var cnt = 0;
var file;
/* Check the quantity of files in a folder to see if you need to combine the sheets and create a file*/
while (files.hasNext()) {
cnt++;
file = files.next();
//Logger.log(file.getName());
if (cnt > BaseNumberOfFilesInFolder) {
Logger.log("File already exists")
break;
}
;
};
// If a file needs to be created this checks it and creates it
Logger.log(cnt + " is the number of files '"+ newSpreadSheetName+ "' has.");
if (cnt > BaseNumberOfFilesInFolder){
Logger.log("No file needs to be created")
}else{
/* Create the new spreadsheet that you store other sheets */
var newSpreadSheet = SpreadsheetApp.create(newSpreadSheetName);
/* Iterate over the spreadsheets over the folder */
while(spreadSheets.hasNext()) {
var sheet = spreadSheets.next();
/* Open the spreadsheet */
var spreadSheet = SpreadsheetApp.openById(sheet.getId());
/* Get all its sheets */
for(var y in spreadSheet.getSheets()) {
/* Copy the sheet to the new merged Spread Sheet */
spreadSheet.getSheets()[y].copyTo(newSpreadSheet);
/* In order to move the file to the folder we want and because
google considers the SpreadSheet a Google Spreadsheet
instead of a file, we have to convert the SpreadSheet to a file in
order to move it.Thats what the next 2 lines of code do.*/
var getNewSSid = newSpreadSheet.getId();
var SStoGFile = DriveApp.getFileById(getNewSSid);
/* Actually moving the file*/
DriveApp.getFolderById(folderId).addFile(SStoGFile);
/* Deleting the duplicate file that was created in the process*/
var rootFolder = DriveApp.getRootFolder();
DriveApp.getRootFolder().removeFile(SStoGFile);
}
}
/*Code to add our base analysis template to the new combined template that was created;*/
//Grabs the template
var sss = SpreadsheetApp.openById(NewSSAnalyzerSheetId); //replace with source ID
var ss = sss.getSheetByName('Analysis Tab'); //replace with source Sheet tab name
var range = ss.getRange('A1:E6'); //assign the range you want to copy
var data = range.getValues();
//Copies the template info into the current newly created sheet
var tssId = newSpreadSheet.getId();
var tss = SpreadsheetApp.openById(tssId); //replace with destination ID
var ts = tss.getSheetByName('Sheet1'); //replace with destination Sheet tab name
ts.getRange(1,1,6,5).setValues(data); //you will need to define the size of the copied data see getRange()
Logger.log("New File was created with the name - " + newSpreadSheetName)
}
};
/* End SpreadSheet Combiner >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
getSubFolders(child);
}
/* Necessary Function to help the above loop through the sub folders */
function getSubFolders(parent) {
parent = parent.getId();
var childFolder = DriveApp.getFolderById(parent).getFolders();
while(childFolder.hasNext()) {
var child = childFolder.next();
Logger.log(child.getName());
getSubFolders(child);
}
return;
}
/* End iterate through Sub Folders */
如何而不是使用SpreadsheetApp.create()使用DriveApp.getFileById().makeCopy()?然后,当您制作电子表格时,您可以在电子表格中使用您想要的任何脚本。
相关文章:
- 当包含另一个asp文件时,是否也包含所有引用的样式和脚本页面
- 借助asp.net验证或java脚本对多个文本进行验证
- chrome扩展:尽管运行了at:documentidle,js脚本还是过早启动
- Java脚本时间添加
- 不显示带有本地json文件数据的谷歌地图脚本
- JQuery添加元素需要在我的js之前再次添加JQuery脚本
- 从远程脚本获取用户IP
- 如何根据时间运行不同的脚本
- 如何将字符串值从php页面发送到java脚本页面
- 使用谷歌应用程序脚本将服务器端数据表返回到客户端
- 可以设置“;文件名"发生错误时显示的内联脚本标记的
- 当脚本由system.js加载时,如何要求('electron')
- HTML标记在脚本标记中工作
- 是否可以控制获取哪些Google地图脚本(JavaScript API)
- 使用谷歌应用程序脚本从工作表中获取值并将其显示在文本框中
- 显示时间的脚本
- ng应用程序使脚本无限运行
- 如何在运行时在angular 2中加载外部js脚本
- Google Adsense多次加载脚本
- 我如何创建一个新的gsheet与脚本已经在它