英文:
having trouble with getting google script to move file into newly created folder within same script
问题
如上所述
我正在尝试创建一个脚本,该脚本将执行以下操作:
根据F8单元格的值创建一个文件夹 - 完成并且正常工作
在另一个表格中为该文件夹创建一个超链接 - 完成并且正常工作
创建正在运行脚本的工作表的副本并将其放入创建的文件夹中 - 令我发狂
以下是我的代码,任何帮助将不胜感激
function foler() {
const parent = DriveApp.getFolderById("1hEjlSMhyvKCuL5Pot-FF80-37IKjcIjd");
var vs = SpreadsheetApp.getActiveSheet();
var value = vs.getRange("F8").getValue();
var sss = SpreadsheetApp.openById('1Gu8NjKdqfrxtxdXJX00UzexgCuVB75QaKdGqjix2yUY');
const sh = sss.getSheetByName('Estimator Quoting');
var aValues = sh.getRange("A1:A").getValues();
var aLast = aValues.filter(String).length;
// 考虑检查等大小数组中的此r&c是否具有来自getFormulas()的公式
if(value) {
var newLink = getLinkFrFolderName_(parent, value);
// 使用当前数组索引仅将此公式写入正确的单元格。
sh.getRange('A' + (aLast + 1)).setFormula("HYPERLINK(\"" + newLink + "\", \"" + value + "\")");
var file = SpreadsheetApp.getActiveSpreadsheet().getId();
var vs = SpreadsheetApp.getActiveSheet();
var valu = vs.getRange("F8").getValue();
var folders = DriveApp.getFoldersByName(valu);
var destFolder = DriveApp.getFolderById(folders);
DriveApp.getFileById(file).makeCopy("Frame, Truss and Take off", destFolder);
}
}
这是创建文件夹的代码
function getLinkFrFolderName_(root, name) {
var folder;
const search = root.getFoldersByName(name);
if (search.hasNext()) {
folder = search.next();
if (search.hasNext())
console.warn("Multiple folders named '" + name + "' in root folder '" + root.getName() + "'");
}
else {
folder = root.createFolder(name);
["Quotes", "Plans"].forEach(function(e) {
folder.createFolder(e);
});
return folder.getUrl();
}
}
英文:
as stated above
I'm trying to create a script that will do the following:
create a folder based on the value of F8 - Done and working
place a hyperlink for the folder in a separate sheet - done and working
create a copy of the sheet that the script is running on and place it in the folder it created - sending me insane
below is my code and any help would be awesome
function foler() {
const parent = DriveApp.getFolderById("1hEjlSMhyvKCuL5Pot-FF80-37IKjcIjd");
var vs = SpreadsheetApp.getActiveSheet();
var value = vs.getRange("F8").getValue();
var sss = SpreadsheetApp.openById('1Gu8NjKdqfrxtxdXJX00UzexgCuVB75QaKdGqjix2yUY');
const sh = sss.getSheetByName('Estimator Quoting');
var aValues = sh.getRange("A1:A").getValues();
var aLast = aValues.filter(String).length;
// Consider checking if this r & c has a formula in the equal size array from getFormulas()
if(value) {
var newLink = getLinkFrFolderName_(parent, value);
// Use the current array index to write this formula in only the correct cell.
sh.getRange('A' + (aLast + 1)).setFormula("HYPERLINK(\"" + newLink + "\", \"" + value + "\")"
);
var file = SpreadsheetApp.getActiveSpreadsheet().getId();
var vs = SpreadsheetApp.getActiveSheet();
var valu = vs.getRange("F8").getValue();
var folders = DriveApp.getFoldersByName(valu);
var destFolder = DriveApp.getFolderById(folders);
DriveApp.getFileById(file).makeCopy("Frame, Truss and Take off", destFolder);
}
and this is the code that creates the folder
function getLinkFrFolderName_(root, name) {
var folder;
const search = root.getFoldersByName(name);
if (search.hasNext()) {
folder = search.next();
if (search.hasNext())
console.warn("Multiple folders named '" + name + "' in root folder '" + root.getName() + "'");
}
else {
folder = root.createFolder(name);
["Quotes", "Plans"].forEach(function(e) {
folder.createFolder(e);
});
return folder.getUrl();
}
答案1
得分: 1
我搞清楚了,请查看以下修复后的代码:
function folder() {
const parent = DriveApp.getFolderById("xxxxxx");
var vs = SpreadsheetApp.getActiveSheet();
var value = vs.getRange("F8").getValue();
var sss = SpreadsheetApp.openById('xxxxxx');
const sh = sss.getSheetByName('Estimator Quoting');
var aValues = sh.getRange("A1:A").getValues();
var aLast = aValues.filter(String).length;
// 考虑检查此 r 和 c 是否在 getFormulas() 返回的等大小数组中有公式。
if(value) {
var newLink = getLinkFrFolderName_(parent, value);
// 使用当前数组索引仅在正确的单元格中写入此公式。
sh.getRange('A' + (aLast + 1)).setFormula("HYPERLINK(\"" + newLink + "\", \"" + value + "\")");
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var TestRange = sheet.getRangeByName('Sheet1');
Logger.log(TestRange);
var id = DriveApp.getFoldersByName(value).next().getId();
var destFolder = DriveApp.getFolderById(id);
DriveApp.getFileById(sheet.getId()).makeCopy("Frame and Truss", destFolder);
};
}
英文:
i figured it out, see below for the fixed code
function folder() {
const parent = DriveApp.getFolderById("xxxxxx");
var vs = SpreadsheetApp.getActiveSheet();
var value = vs.getRange("F8").getValue();
var sss = SpreadsheetApp.openById('xxxxxx');
const sh = sss.getSheetByName('Estimator Quoting');
var aValues = sh.getRange("A1:A").getValues();
var aLast = aValues.filter(String).length;
// Consider checking if this r & c has a formula in the equal size array from getFormulas()
if(value) {
var newLink = getLinkFrFolderName_(parent, value);
// Use the current array index to write this formula in only the correct cell.
sh.getRange('A' + (aLast + 1)).setFormula("HYPERLINK(\"" + newLink + "\", \"" + value + "\")"
);
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var TestRange = sheet.getRangeByName('Sheet1');
Logger.log(TestRange);
var id = DriveApp.getFoldersByName(value).next().getId();
var destFolder = DriveApp.getFolderById(id);
DriveApp.getFileById(sheet.getId()).makeCopy("Frame and Truss", destFolder);
};
}
````
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论