用于将自动生成的谷歌文档URL写入电子表格的脚本

Question

我有一个Google表单，在点击提交按钮时做两件事。首先，它将数据转储到电子表格中，然后它将表单中的信息自动填充到Google Doc模板中。

在我的脚本中自动填充谷歌文档，我已经抓取了谷歌文档的URL。但我需要写这个URL到最后一行的列J在我的谷歌表。更正:使用getActiveSheet函数很好，我忘了这个脚本是从(活动)谷歌表运行的(抱歉!)。

有人能帮忙吗?下面是获取URL的脚本片段:

function autoFillGoogleDocFromForm(e) {
//e.values is an array of form values
var TimeStamp = e.values[0];
var Technician = e.values[1];
var Vendor = e.values[2];
var xxx = e.values[3];
var yyy = e.values[4];
var SerialNumber = e.values[5];
var AssetTag = e.values[6];
var TicketNumber = e.values[7];
var HostName = e.values[8];
var DocumentLink = e.values[9];
var Return = e.values[10];
var Platform = e.values[11];
var Summary = e.values[12];
var URL = "";

//file is the template file, and you get it by ID
var file = DriveApp.getFileById('aaa'); 

//Put auto filled Google Doc into the appropriate Vendor Folder
//file.makeCopy will return a Google Drive file object
if (Vendor == "111") {
var folder = DriveApp.getFolderById('bbb')
var copy = file.makeCopy(TicketNumber + ' - ' + SerialNumber, folder); 
}

if (Vendor == "222") {
var folder = DriveApp.getFolderById('ccc')
var copy = file.makeCopy(TicketNumber + ' - ' + SerialNumber, folder); 
}

if (Vendor == "333") {
var folder = DriveApp.getFolderById('ddd')
var copy = file.makeCopy(TicketNumber + ' - ' + SerialNumber, folder); 
}

//Once we've got the new file created, we need to open it as a document by using its ID
var doc = DocumentApp.openById(copy.getId()); 
//Get the url of the newly created Google Doc
var url = doc.getUrl();
//Script to write this URL into the Shared Google Sheet will go here
//Since everything we need to change is in the body, we need to get that
var body = doc.getBody(); 

//Then we call all of our replaceText methods
body.replaceText('{{EmailAddress}}', Technician);
body.replaceText('{{TicketNumber}}', TicketNumber);  
body.replaceText('{{HostName}}', HostName); 
body.replaceText('{{SerialNumber}}', SerialNumber); 
body.replaceText('{{AssetTag}}', AssetTag); 
body.replaceText('{{Summary}}', Summary); 
body.replaceText('{{Vendor}}', Vendor); 
body.replaceText('{{URL}}', url); 

//Lastly we save and close the document to persist our changes
doc.saveAndClose(); 

提前感谢!

Answer 1

从你问题中的以下情况来看，

这也是一个共享的谷歌表，所以我不能使用getActiveSheet函数，我需要引用谷歌表URL，我相信。

如果您有权限将值写入共享的Google电子表格，那么下面的修改如何?

:

var url = doc.getUrl();
//Script to write this URL into the Shared Google Sheet will go here

:

var url = doc.getUrl();
//Script to write this URL into the Shared Google Sheet will go here
var sheet = SpreadsheetApp.openById("### Spreadsheet ID ###").getSheetByName("### sheet name ###");
sheet.appendRow([url]);

• 在本次修改中，请设置电子表格ID和表格名称。这样，url的值被附加到Google电子表格中工作表最后一行的下一行。
• 如果您想使用电子表格URL而不是电子表格ID，请将openById("### Spreadsheet ID ###")修改为openByUrl("### Spreadsheet URL ###")。

引用:

• openById (id)
• openByUrl (url)
• appendRow (rowContents)

编辑:

从以下回复中，

这似乎不工作插入到J列的最后一行不幸的是。看起来这个脚本可以使用活动表单，毕竟，我将编辑OP.

在这种情况下，下面的修改如何?

:

var url = doc.getUrl();
//Script to write this URL into the Shared Google Sheet will go here

:

var url = doc.getUrl();
//Script to write this URL into the Shared Google Sheet will go here
// var sheet = SpreadsheetApp.openById("### Spreadsheet ID ###").getSheetByName("### sheet name ###");
var sheet = SpreadsheetApp.getActiveSheet(); // or SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheetname")
sheet.getRange("J" + (sheet.getLastRow() + 1)).setValue(url);

或

var url = doc.getUrl();
//Script to write this URL into the Shared Google Sheet will go here
// This is from https://stackoverflow.com/a/44563639/7108653
Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
const range = this.getRange(offsetRow, columnNumber, 2);
const values = range.getDisplayValues();
if (values[0][0] && values[1][0]) {
return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
} else if (values[0][0] && !values[1][0]) {
return offsetRow + 1;
}
return offsetRow;
};
// var sheet = SpreadsheetApp.openById("### Spreadsheet ID ###").getSheetByName("### sheet name ###");
var sheet = SpreadsheetApp.getActiveSheet(); // or SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheetname")
sheet.getRange("J" + sheet.get1stEmptyRowFromTop(10)).setValue(url);

Answer 2

建议:

如果您需要将URL值写入共享的Google电子表格文件中J列的最后一行，您可以尝试下面的示例:

function sample() {
var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/SHEET_ID_IS_HERE/edit#gid=0');
var sheet = ss.getSheets()[0]; // access first sheet
var activeSheet = ss.setActiveSheet(sheet);
//Sample setValue() action to the shared sheet file
activeSheet.getRange("A1").setValue("URL");
}

注意:您需要对正在访问的共享Google Sheet文件具有编辑权限

引用:

• openByUrl (url)
• getSheets ()
• setActiveSheet(表)

Answer 3

这是最终为我工作的代码。

//Get the url of the newly created Google Doc
var url = doc.getUrl();
// var sheet = SpreadsheetApp.openById("### Spreadsheet ID ###").getSheetByName("### sheet name ###");
var sheet = SpreadsheetApp.getActiveSheet(); // or SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheetname")
sheet.getRange("J" + (sheet.getLastRow())).setValue(url);