遇到了将Google脚本中的文件移动到新创建的文件夹的问题。

huangapple go评论66阅读模式
英文:

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>



huangapple
  • 本文由 发表于 2023年3月7日 13:59:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/75658488.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定