IAmMoonie

Google Doc Word Count for list of URLs in a sheet

Jan 9th, 2023
1,075
1
Never
1
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
JavaScript 3.25 KB | Source Code | 1 0
  1. /* The ID of the Google Sheet that you want to write the word count to. */
  2. const SHEET_ID = "SheetIDGoesHere";
  3.  
  4. /**
  5.  * It gets the URLs of Google Docs from column A of the first sheet in a Google Sheet, then it gets the
  6.  * word count of each Google Doc and writes it to column B of the same row as the URL.
  7.  */
  8. function countWords() {
  9.   /* Get the Google Sheet. */
  10.   const sheet = SpreadsheetApp.openById(SHEET_ID);
  11.  
  12.   /* It gets the first sheet in the Google Sheet and gets the values of column A, filtering out the
  13.   empty rows. */
  14.   const firstSheet = sheet.getSheets()[0];
  15.   const urlValues = firstSheet
  16.     .getRange("A2:A")
  17.     .getValues()
  18.     .filter((row) => row[0]);
  19.  
  20.   /* Converting the 2D array `urlValues` into a 1D array `urls`. */
  21.   const urls = [].concat(...urlValues);
  22.   let wordCounts = [];
  23.  
  24.   /* Getting the URLs of Google Docs from column A of the first sheet in a Google Sheet, then it gets
  25.   the word count of each Google Doc and writes it to column B of the same row as the URL. */
  26.   for (const url of urls) {
  27.     /* Getting the document ID from the URL. */
  28.     const documentId = url.match(/[-\w]{25,}/);
  29.  
  30.     /* Opening the Google Doc with the ID `documentId`. */
  31.     const document = DocumentApp.openById(documentId);
  32.  
  33.     /* Initializing the variable `wordCount` to 0. */
  34.     let wordCount = 0;
  35.  
  36.     /* Config for the headings and titles in the document to be excluded them from the word count.
  37.     This is the same functionality that Google Docs uses for wordcount */
  38.     const searchType = DocumentApp.ElementType.PARAGRAPH;
  39.     const searchHeadings = [
  40.       DocumentApp.ParagraphHeading.HEADING1,
  41.       DocumentApp.ParagraphHeading.HEADING2,
  42.       DocumentApp.ParagraphHeading.HEADING3,
  43.       DocumentApp.ParagraphHeading.HEADING4,
  44.       DocumentApp.ParagraphHeading.HEADING5,
  45.       DocumentApp.ParagraphHeading.HEADING6,
  46.       DocumentApp.ParagraphHeading.TITLE
  47.     ];
  48.     let searchResult = null;
  49.  
  50.     /* Getting the headings and titles in the document to be excluded them from the word count.
  51.     This is the same functionality that Google Docs uses for wordcount. */
  52.     while (
  53.       (searchResult = document.getBody().findElement(searchType, searchResult))
  54.     ) {
  55.       const par = searchResult.getElement().asParagraph();
  56.       if (searchHeadings.includes(par.getHeading())) {
  57.         /* Subtracting the word count of the heading from the total word count. */
  58.         wordCount -= par.getText().split(" ").length;
  59.       }
  60.     }
  61.  
  62.     /* It gets the paragraphs in the document. */
  63.     const paragraphs = document.getBody().getParagraphs();
  64.  
  65.     /* Getting an individual paragraph from all the paragraphs and counting its words. */
  66.     for (const paragraph of paragraphs) {
  67.       /* It gets the text of the paragraph. */
  68.       const paragraphText = paragraph.getText();
  69.  
  70.       /* Getting the word count of the paragraph and adding it to the total word count. */
  71.       wordCount += paragraphText.split(" ").length;
  72.     }
  73.     /* Pushing the word count of the Google Doc to the array `wordCounts`. */
  74.     wordCounts.push([wordCount]);
  75.   }
  76.  
  77.   /* Writing the word count to Column B of the same row as the URL. */
  78.   firstSheet
  79.     .getRange(`B2:B${(wordCounts.length + 1).toString()}`)
  80.     .setValues(wordCounts);
  81. }
Advertisement
Comments
  • # text 0.16 KB | 0 0
    1. Just want to say thanks! I've been wanting to use a script like this for a while but didn't know how to do it. I found this via your reddit post. It works great!
Add Comment
Please, Sign In to add comment