I am now need to convert the Google Sheet page to PDF, email to user and save the PDF format straightway to Google Drive.
And i need the Google Drive link after save it to Google Drive.
The steps from convert the Google Sheet to PDF, and i've done but I've stuck at getting the URL to be paste on the specific cells.
i know to get the URL using this code Logger.log(fileUrl)
But how to paste on cell the command ?
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);
};
};
}
If cell B3 value in First Source is find in Google Drive, paste the URL in Column DX where the AI is same with First Source.
CodePudding user response：
I believe your goal is as follows.
- You want to search the file of filename
subjectretrieved from the cell "B3" of "Source Email-Boom" sheet from your Google Drive, and when the value ofsubjectis found from the column "AI" of "BL-Inspection Report" sheet, you want to put the URL of the file to the column "AJ". - For my question of
For example, you want to put the URL of the just created file?, fromYesof your replying, I understood that you wanted to put the URL of the just created file in this script.
In this case, how about the following modification? I thought that in this case, the file URL of the just created file can be directly retrieved from DriveApp.getFolderById(folderID).createFile(response).setName(nameFile). So, how about the following modification?
From:
DriveApp.getFolderById(folderID).createFile(response).setName(nameFile);
To:
var fileUrl = DriveApp.getFolderById(folderID).createFile(response).setName(nameFile).getUrl();
And also, please modify as follows.
From:
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);
};
};
To:
var range = sheet2.getRange("AI2:AI" sheet2.getLastRow()).createTextFinder(subject).findNext();
if (range) {
range.offset(0, 1).setValue(fileUrl);
}
- In this modification, the cell is searched using TextFinder.