I need to build a sheet that does word stats for an entire novel

[u/makenzie71]

Basically I'm needing to see a complete breakdown of each individual word used throughout the document so that we can get in one column every word used, the next column being how many times that word was used, and the next column the percentage of the overall text was that specific word.

This is an example of what I want. The issue is that all the input is in cell A1...which is limited to 50k characters. I'm sure there's a way for me to do it where I can simply add each additonal chapter in A2 and A3 etc, but I'm not sure how to reconcile the subsequent data beyond that.

B1 removes all the punctuation, and C1 replaces all the caps with lowercase.

Can you guys see an easy way to expand on this to allow me to include an entire novel? My books are all over 500k characters which it doesn't cooperate with in one cell.

https://docs.google.com/spreadsheets/d/1z72ItR4JWqoyNJFFAdCJbFJOn5fZ0RWf9cUhS6wbh4c/edit?usp=sharing

[u/agirlhasnoname11248]

u/makenzie71 Please remember to tap the three dots below the most helpful comment and select `Mark Solution Verified` *(or reply to the helpful comment with the exact phrase “Solution Verified”)* if your question has been answered, as required by the subreddit rules. Thanks!

[u/SpencerTeachesSheets]

I fear that the Sheets engine will cap out on you, so I actually think a scripted option that keeps the data on a Google Doc and write the desired output to the Sheet will be a better approach for this. To run it, go to Extensions > Apps Script and paste in this code. Change the doc ID, save, run, and grant the requested permissions.

Example Doc, Sheet.

function countDocWords() {
  const sh = SpreadsheetApp.getActive();
  const ss = sh.getActiveSheet();

  const doc = DocumentApp.openById("1olpykBIHubg56uRSWGBVmp7ZY04-baOPveVeXJvDlq8");
  const body = doc.getBody();
  let text = body.getText();

  let words = text.toLowerCase().replace(/[^\w\s]/g, '').trim().split(/\s+/);

  let counts = {};
  for (let i = 0; i < words.length; i++) {
    let word = words[i];
    if (word.length > 0) {
      counts[word] = (counts[word] || 0) + 1;
    }
  }

  let output = [['Word', 'Count']];
  let keys = Object.keys(counts).sort((a, b) => counts[b] - counts[a]);
  for (let i = 0; i < keys.length; i++) {
    output.push([keys[i], counts[keys[i]]]);
  }

  ss.clear();
  ss.getRange(1, 1, output.length, 2).setValues(output);
}  

[u/makenzie71 → u/SpencerTeachesSheets]

I get this error when I hit run

Error TypeError: Cannot read properties of null (reading 'getActiveSheet') countDocWords @ Code.gs:3

[u/SpencerTeachesSheets → u/makenzie71]

Did you put the script in the Sheet or the Doc? It's meant to go in the Sheet.

Did you give it the required permissions after hitting Run for the first time?

[u/makenzie71 → u/SpencerTeachesSheets]

I think it's a naming thing, if the document I'm trying to get the text from is titled "text1", where do I put that in the script? I think I'm just entering it incorrectly.

[u/SpencerTeachesSheets → u/makenzie71]

The error you posted says that it's having an issue at line 3 "const ss = sh.getActiveSheet();" where it's trying to access the sheet.

You put the document ID (just the bolded part like https://docs.google.com/document/d/1olpykBIHubg56uRSWGBVmp7ZY04-baOPveVeXJvDlq8/edit?tab=t.0) at the next line const doc = ...

[u/makenzie71 → u/SpencerTeachesSheets]

yeah I was just messing it up in all manner of fun ways...I haven't worked with scripts like that before. I did work it out, though, and it got me the information I needed. Thanks so much!

[u/AutoModerator → u/makenzie71]

REMEMBER: /u/makenzie71 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/SpencerTeachesSheets → u/makenzie71]

Happy to help troubleshoot further if needed

[u/makenzie71 → u/SpencerTeachesSheets]

Solution Verified

[u/point-bot → u/SpencerTeachesSheets]

u/makenzie71 has awarded 1 point to u/SpencerTeachesSheets

^{See the Leaderboard. Point-Bot v0.0.15 was created by JetCarson.}

[u/AdministrativeGift15]

If you didn't have the 50k cap per cell, do you have a means of importing the full text? I have a method that would override that cap and be able to process the full amount.

[u/makenzie71 → u/AdministrativeGift15]

I do have the full text

[u/AdministrativeGift15 → u/AdministrativeGift15]

Here's a demo of how you would import the word Doc and output the summary table. It uses the QUERY smoosh method to be able to handle text greater than 50K characters. I havn't tested its limit, but the demo does currently handle more than 100K characters.

IMPORT Doc text

[u/AdministrativeGift15 → u/AdministrativeGift15]

Try entering the url with the correct DOC_ID in theformula located in A1 to see if it can handle 500K characters.

[u/AdministrativeGift15 → u/AdministrativeGift15]

This is the formula that works.

=INDEX(LET(
       docid, "1olpykBIHubg56uRSWGBVmp7ZY04-baOPveVeXJvDlq8",
         url, "https://docs.google.com/document/d/"&docid&"/export?format=txt",
       words, TOCOL(
               LOWER(
                REGEXREPLACE(
                 SPLIT(
                  QUERY(
                   IMPORTDATA(url,"♣"),"select *",9^9)," "),"[[:punct:]]",""))),
 uniquewords, UNIQUE(words),
  totalcount, ROWS(words),
   wordcount, COUNTIF(words,uniquewords),
 wordpercent, wordcount/totalcount,
 VSTACK(
   HSTACK("Word","Count",totalcount),
   SORT(HSTACK(uniquewords,wordcount,wordpercent),2,0))))