0

Question - I have a Google Sheet with about 200 Google Doc URLS. Is it possible to have a script that will convert the URLS to individual PDF files and save it to my desktop?

I have searched the internet high and low and I cannot find a solution. If anyone has any insight or can point me in the right direction, that would be really helpful.

Michael LaMarche
  • 45
  • 7
  • I have 2 questions for understanding your question. 1. What language do you want to use? 2. Can you provide the sample Google Document URLs? – Tanaike Mar 07 '22 at 03:03
  • Sure- So here is the Google sheet with three google DOC uRL examples that I would like converted into PDF. https://docs.google.com/spreadsheets/d/1mOJ961BBcjlKEW71ML3hb_bZJDVTAizg12WHLc2AKUI/edit?usp=sharing If possible I would like to use Javascript but am open to any suggestions that would save me from having to convert each of the 200 or so doc manually. Thank you again so much. – Michael LaMarche Mar 07 '22 at 11:55

1 Answers1

1

One solution will be, create a folder inside of Drive, convert the documents to PDFs, and download the folder as a .zip.

function convertDocuments() {
  /* Select the Spreadsheet */
  const SS_ID = "SPREADSHEET_ID"
  const SS = SpreadsheetApp.openById(SS_ID)
  const PDF_MIME = "application/pdf"
  const newFolder = DriveApp.createFolder('PDFs')
  /* Get the links */
  const getLinks = SS.getRange('A2:A').getValues()
  getLinks.forEach((cells)=>{
    const link = cells[0]
    if(link==="") return
    /* Getting the ID from the URL */
    const parseID = link.toString().split("/")[5]
    /* CREATE THE PDF */
    const document = DriveApp.getFileById(parseID).getAs(PDF_MIME).copyBlob()
    /* Inserting the PDF into the file */
    newFolder.createFile(document)
  })
  Logger.log(newFolder.getUrl())
  /* downloadFolder(newFolder.getId()) */
}

The steps are as follows:

  1. Retrieve all links inside the A column
  2. Use DriveApp in order to create a PDF for every link (link needs to be parsed for retrieving the ID)
  3. Place the PDF inside the Drive Folder
  4. From here, you have two possibilities:
    • Use the UI to download the folder
    • Use this function (provided by @Tanaike) to get directly the download link. Inside my script is referenced as downloadFolder
function downloadFolder(folderId) {
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFiles();
  let blobs = [];
  while (files.hasNext()) {
    blobs.push(files.next().getBlob());
  }
  const zipBlob = Utilities.zip(blobs, folder.getName() + ".zip");
  const fileId = DriveApp.createFile(zipBlob).getId();
  const url = "https://drive.google.com/uc?export=download&id=" + fileId;
  Logger.log(url);
}
Documentation
Emel
  • 2,283
  • 1
  • 7
  • 18
  • When I try to run the first script I am getting the error message Exception: Unexpected error while getting the method or property openById on object SpreadsheetApp. convertDocuments @ Code.gs:4 – Michael LaMarche Mar 07 '22 at 14:47
  • 1
    Have you changed the `SPREADSHEET_ID` by your Spreadsheet ID? – Emel Mar 07 '22 at 14:49
  • When debugging it stops at the second const const SS = SpreadsheetApp.openById(SS_ID) – Michael LaMarche Mar 07 '22 at 14:51
  • I did and am still getting the same error – Michael LaMarche Mar 07 '22 at 14:51
  • 1
    That's strange, the [openById(ID)](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openbyidid) method should not fail. With the example you shared, it should be `const SS_ID = "1mOJ961BBcjlKEW71ML3hb_bZJJDVTAizg12WHLc2AKUI"`. – Emel Mar 07 '22 at 14:54
  • My apologies, I had a / that I accidentally included. – Michael LaMarche Mar 07 '22 at 14:55
  • 1
    So, the script is working? If so, please, consider accepting my answer. – Emel Mar 07 '22 at 14:55
  • @Emel- how can I get the function to skip URLS that it cant convert to a PDF? Right now, the program stops, I have to debug then find the link and remove it. Then I have to run the program again. Is there a way for it to skip over the links it cannot convert? – Michael LaMarche Mar 07 '22 at 15:48
  • 1
    You can encapsulate the failing function inside a [`try{}catch{}`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/try...catch) block. – Emel Mar 07 '22 at 16:19