我現在需要將 Google Sheet 頁面轉換為 PDF，通過電子郵件發送給用戶并將 PDF 格式直接保存到 Google Drive。
將其保存到 Google Drive 后，我需要 Google Drive 鏈接。
將 Google Sheet 轉換為 PDF 的步驟，我已經完成了，但我一直堅持將 URL 粘貼到特定的單元格上。
我知道使用此代碼獲取 URLLogger.log(fileUrl)
但是如何將命令粘貼到單元格上？
var changedFlag = false;
var TEMPLATESHEET='Boom-Report';
function emailSpreadsheetAsPDF() {
//Utilities.sleep(300000); //to pause for 60 seconds . Make sure photo completely upload to google sheet
DocumentApp.getActiveDocument();
DriveApp.getFiles();
// This is the link to my spreadsheet with the Form responses and the Invoice Template sheets
// Add the link to your spreadsheet here
// or you can just replace the text in the link between "d/" and "/edit"
// In my case is the text: 17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI
const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1NVJOdFLBAgNFqSHhnHJYybjUlSqhv4hKI_HXJyhJ88E/edit");
// We are going to get the email address from the cell "B7" from the "Invoice" sheet
// Change the reference of the cell or the name of the sheet if it is different
const value = ss.getSheetByName("Source Email-Boom").getRange("X3").getValue();
const email = value.toString();
// Subject of the email message
const subject = ss.getSheetByName("Source Email-Boom").getRange("B3").getValue();
// Email Text. You can add HTML code here - see ctrlq.org/html-mail
const body = "Boom Lifts Inspection Report - Sent via Auto Generate PDI Report from Glideapps";
// Again, the URL to your spreadsheet but now with "/export" at the end
// Change it to the link of your spreadsheet, but leave the "/export"
const url = 'https://docs.google.com/spreadsheets/d/1NVJOdFLBAgNFqSHhnHJYybjUlSqhv4hKI_HXJyhJ88E/export?';
const exportOptions =
'exportFormat=pdf&format=pdf' // export as pdf
'&size=A4' // paper size letter / You can use A4 or legal
'&portrait=true' // orientation portal, use false for landscape
'&fitw=true' // fit to page width false, to get the actual size
'&sheetnames=false&printtitle=false' // hide optional headers and footers
'&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
'&fzr=false' // do not repeat row headers (frozen rows) on each page
'&gid=1832955909'; // the sheet's Id. Change it to your sheet ID.
// You can find the sheet ID in the link bar.
// Select the sheet that you want to print and check the link,
// the gid number of the sheet is on the end of your link.
var params = {method:"GET",headers:{"authorization":"Bearer " ScriptApp.getOAuthToken()}};
// Generate the PDF file
var response = UrlFetchApp.fetch(url exportOptions, params).getBlob();
// Send the PDF file as an attachement
GmailApp.sendEmail("[email protected]", subject, body, {
htmlBody: body,
attachments: [{
fileName: ss.getSheetByName("Source Email-Boom").getRange("B3").getValue().toString() ".pdf",
content: response.getBytes(),
mimeType: "application/pdf"
}]
});
// Save the PDF to Drive. (in the folder) The name of the PDF is going to be the name of the Company (cell B5)
const nameFile = ss.getSheetByName("Source Email-Boom").getRange("B3").getValue().toString() ".pdf"
const folderID = "1ZKWq9jWmeEQlxncuTPHssCFXC3Fidmxn";
DriveApp.getFolderById(folderID).createFile(response).setName(nameFile);
// create file URL
var SpreadsheetID = "1NVJOdFLBAgNFqSHhnHJYybjUlSqhv4hKI_HXJyhJ88E";
var ss2 = SpreadsheetApp.openById(SpreadsheetID);
var Sheetname2= "BL-Inspection Report";
var sheet2 = ss2.getSheetByName(Sheetname2);
// Get the last row based on the data range of a single column.
var lastRow2 = sheet2.getLastRow();
var lastColumn2 = sheet2.getLastColumn();
//EXAMPLE: Get the data range based on our selected columns range.
var dataRange2 = sheet2.getRange(1,1, lastRow2, lastColumn2);
var dataValues2 = dataRange2.getValues();
var dataMatch=[];
//***** */
// Loop through array and if condition met, add relevant
// background color.
var p=34 ; //Column No. for Name column AI:AI (Report No)
var filename = encodeURI(nameFile);
var files = DriveApp.getFilesByName(nameFile);
while (files.hasNext()) {
var file = files.next();
if (file) {
var fileUrl = file.getUrl();
};
};
////////////////HELP THIS PART////////////////////////////////
for ( j = 0 ; j < lastRow2 ; j ){
var zz=j;
var yy=dataValues2[j][34];
if(dataValues2[j][34] == subject){
var doclink = Logger.log(fileUrl);
var range = sheet2.getRange(j 1, 128);
range.setValue(doclink);
};
};
}
如果在 Google Drive 中找到 First Source 中的單元格 B3 值，請將 URL 粘貼到 DX 列中，其中 AI 與 First Source 相同。
uj5u.com熱心網友回復：
我相信你的目標如下。
- 您想
subject從 Google Drive 中搜索從“Source Email-Boom”表的單元格“B3”中檢索到的檔案名檔案，并且當subject從“BL-Inspection Report”表的“AI”列中找到 的值時，您想將檔案的 URL 放入“AJ”列。 - 對于我的問題
For example, you want to put the URL of the just created file?，從Yes您的回復中，我了解到您想將剛剛創建的檔案的 URL 放在此腳本中。
在這種情況下，如何進行以下修改？我以為在這種情況下，可以直接從DriveApp.getFolderById(folderID).createFile(response).setName(nameFile). 那么，下面的修改呢？
從：
DriveApp.getFolderById(folderID).createFile(response).setName(nameFile);
到：
var fileUrl = DriveApp.getFolderById(folderID).createFile(response).setName(nameFile).getUrl();
另外，請進行如下修改。
從：
var filename = encodeURI(nameFile);
var files = DriveApp.getFilesByName(nameFile);
while (files.hasNext()) {
var file = files.next();
if (file) {
var fileUrl = file.getUrl();
};
};
////////////////HELP THIS PART////////////////////////////////
for (j = 0; j < lastRow2; j ) {
if (dataValues2[j][34] == subject) {
var doclink = Logger.log(fileUrl);
var range = sheet2.getRange(j 1, 128);
range.setValue(doclink);
};
};
到：
var range = sheet2.getRange("AI2:AI" sheet2.getLastRow()).createTextFinder(subject).findNext();
if (range) {
range.offset(0, 1).setValue(fileUrl);
}
- 在此修改中，使用 TextFinder 搜索單元格。
參考：
- 創建文本查找器（查找文本）
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/410359.html
標籤：