通过名称获取电子表格网址并粘贴到另一个电子表格中的代码

Question

因此，我有一个包含2列的工作表，A列是电子表格的名称，B列是URL。因此，我正在使用脚本通过名称来获取url，但最后一行不起作用。谁能帮我？

function spreadsheetUrl() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  ss.setActiveSheet(ss.getSheetByName('Page1'));

  var lastRow = ss.getRange("B2:B200").getLastRow();

  var newRow = lastRow+1;

  var range = SpreadsheetApp.getActiveSheet().getRange(newRow, 1);

  var spreadsheetName = range.getValue();

  var files = DriveApp.getFilesByName(spreadsheetName);

  while (files.hasNext()){

     var file = files.next();

     var url = file.getUrl();

     return url

     }

  getValue(url).Paste_Values(getRange(newRow, 2), {contentsOnly: true});

}

Answer 1

获取电子表格URL您的代码将面临的问题之一是，随着添加更多的url，newrow不会改变，实际上，您实际上希望文件名旁边的url不在列表的末尾。此函数将vA1 [i] [0]中的文件名放入urls列，即vA2 [i] [0]，它还会计算每个名称的文件数并记录信息并在对话框中显示在功能的末尾，从而使您有机会确保获得正确的文件。如果您习惯于存储文件备份，则会发生这种情况。还可以考虑将对话框放入循环中，从而让您选择要保存的对话框，但这需要您在函数运行时进行观察。function spreadsheetUrl() {&nbsp; var ss=SpreadsheetApp.getActive();&nbsp; var sh=ss.getSheetByName('Page1');&nbsp; var rg1=sh.getRange(2,1,sh.getLastRow(),1);&nbsp; var rg2=sh.getRange(2,2,sh.getLastRow(),1);&nbsp; var vA1=rg1.getValues();//Spreadsheet Names&nbsp; var vA2=rg2.getValues();//Urls&nbsp; var xf=[];&nbsp; for(var i=0;i<vA1.length;i++) {&nbsp; &nbsp; if(vA1[i][0]) {&nbsp; &nbsp; &nbsp; var files=DriveApp.getFilesByName(vA1[i][0]);&nbsp; &nbsp; &nbsp; var n=0;&nbsp; &nbsp; &nbsp; while (files.hasNext()){&nbsp; &nbsp; &nbsp; &nbsp; var file = files.next();&nbsp; &nbsp; &nbsp; &nbsp; if(n==0) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; vA2[i][0]=file.getUrl();&nbsp; &nbsp; &nbsp; &nbsp; }else{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xf.push({name:file.getName(),url:file.getUrl(),id:file.getId()});&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; n++;&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; }&nbsp; rg2.setValues(vA2);&nbsp; var html='<h1>You have additional files with the same names</h1>';&nbsp; if(xf.length) {&nbsp; &nbsp; for(var i=0;i<xf.length;i++) {&nbsp; &nbsp; &nbsp; html+=Utilities.formatString('<br />Name: %s Url: %s Id: %s',xf[i].name, xf[i].url,xf[i].id);&nbsp; &nbsp; }&nbsp; &nbsp; html+='<br /><input type="button" value="Exit" onClick="google.script.host.close();" />';&nbsp; &nbsp; var userInterface=HtmlService.createHtmlOutput(html).setWidth(1000);&nbsp; &nbsp; SpreadsheetApp.getUi().showModelessDialog(userInterface, "Files with Identical Names")&nbsp; }}通过Dailog进行更改我对此进行了一些尝试，当有多个给定名称的文件时，该版本允许您修改发布的网址。它是在循环的最后而不是在循环的中间进行的，因此您不必在循环运行时执行多次。此版本还具有用于将更改写入工作表服务器端的附带功能。function spreadsheetUrl() {&nbsp; var ss=SpreadsheetApp.getActive();&nbsp; var sh=ss.getSheetByName('Page1');&nbsp; var rg1=sh.getRange(2,1,sh.getLastRow(),1);&nbsp; var rg2=sh.getRange(2,2,sh.getLastRow(),1);&nbsp; var vA1=rg1.getValues();//Spreadsheet Names&nbsp; var vA2=rg2.getValues();//Urls&nbsp; //rg2.clearContent();//debug&nbsp; var xf=[];&nbsp; for(var i=0;i<vA1.length;i++) {&nbsp; &nbsp; if(vA1[i][0]) {&nbsp; &nbsp; &nbsp; var files=DriveApp.getFilesByName(vA1[i][0]);&nbsp; &nbsp; &nbsp; var n=0;&nbsp; &nbsp; &nbsp; while (files.hasNext()){&nbsp; &nbsp; &nbsp; &nbsp; var file = files.next();&nbsp; &nbsp; &nbsp; &nbsp; if(n==0) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; vA2[i][0]=file.getUrl();&nbsp; &nbsp; &nbsp; &nbsp; }else{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xf.push({name:file.getName(),url:file.getUrl(),id:file.getId(),row:i+2});&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; n++;&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; }&nbsp; rg2.setValues(vA2);&nbsp; var html='<h1>You have additional files with the same names</h1>';&nbsp; html+='<br />Change them if you wish by select the select Me button for the appropriate selection.';&nbsp; html+='<br />Press close when you done.';&nbsp; if(xf.length) {&nbsp; &nbsp; for(var i=0;i<xf.length;i++) {&nbsp; &nbsp; &nbsp; html+=Utilities.formatString('<br />Name: %s Id: %s<input type="button" value="Select Me" onClick="selectMe(\'%s\',%s);" />',xf[i].name,xf[i].id,xf[i].url,xf[i].row);&nbsp; &nbsp; }&nbsp; &nbsp; html+='<br /><input type="button" value="Exit" onClick="google.script.host.close();" />';&nbsp; &nbsp; html+='<script>function selectMe(value,row){console.log(value);console.log(row);google.script.run.selectMe("Page1",value,row);}console.log("My Code");</script>';&nbsp; &nbsp; var userInterface=HtmlService.createHtmlOutput(html).setWidth(400).setHeight(300);&nbsp; &nbsp; SpreadsheetApp.getUi().showModelessDialog(userInterface, "Files with Identical Names")&nbsp; }}function selectMe(sheetname,value,row) {&nbsp; var ss=SpreadsheetApp.getActive();&nbsp; var sh=ss.getSheetByName(sheetname);&nbsp; var rg=sh.getRange(row,2).setValue(value);}