AppScript_googledocsfunctions.txt

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Functions')
    .addItem('Fetch Comments', 'showComments')
    .addItem('Fetch Document Author', 'showDocAuthor')
    .addItem('Get Word Count', 'showWordCount')  
    .addItem('Extract URLs', 'extractUrls') // Add this line
    .addToUi();
}

function getCommentsFromDoc(url) {
  Logger.log("Starting getCommentsFromDoc with URL: " + url);
  if (!url) {
    Logger.log("URL is empty.");
    return "URL is empty.";
  }

  var docId = getIdFromUrl(url);
  Logger.log("Extracted docId: " + docId);
  if (!docId) {
    Logger.log("Invalid URL.");
    return "Invalid URL.";
  }

  try {
    var comments = fetchAllComments(docId);
    var output = [];

    Logger.log("Total comments fetched: " + comments.length);

    comments.forEach(function(comment) {
      if (comment.author && comment.author.displayName) {
        var commentText = "{{{" + comment.author.displayName + ": " + (comment.content || '') + "}}}";
        output.push(commentText);
      }

      if (comment.replies && comment.replies.length > 0) {
        comment.replies.forEach(function(reply) {
          if (reply.author && reply.author.displayName) {
            var replyText = "{{{" + reply.author.displayName + ": " + (reply.content || '') + "}}}";
            output.push(replyText);
          }
        });
      }
    });

    Logger.log("Output: " + output.join(" "));
    return output.join(" ");
  } catch (e) {
    Logger.log("Error in getCommentsFromDoc: " + e.message);
    return "Error: " + e.message;
  }
}

function getIdFromUrl(url) {
  Logger.log("Extracting ID from URL: " + url);
  var idMatch = url.match(/[-\w]{25,}/);
  Logger.log("ID extraction result: " + (idMatch ? idMatch[0] : "No match"));
  return idMatch ? idMatch[0] : null;
}

function fetchAllComments(fileId) {
  Logger.log("Fetching comments for fileId: " + fileId);
  var comments = [];
  var pageToken = null;
  var pageCount = 0;

  try {
    do {
      Logger.log("Fetching page " + (pageCount + 1));
      var response = Drive.Comments.list(fileId, {
        fields: "comments(content,author/displayName,replies(content,author/displayName)),nextPageToken",
        includeDeleted: false,
        maxResults: 100,
        pageToken: pageToken
      });

      Logger.log("Response received: " + JSON.stringify(response));

      if (response.comments) {
        comments = comments.concat(response.comments);
        Logger.log("Fetched " + response.comments.length + " comments");
      }

      pageToken = response.nextPageToken;
      pageCount++;

      Logger.log("Next pageToken: " + pageToken);

    } while (pageToken);
  } catch (e) {
    Logger.log("Error in fetchAllComments: " + e.message);
  }

  Logger.log("Total comments fetched: " + comments.length);
  return comments;
}

function showComments() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange();
  var cells = range.getValues();

  cells.forEach(function(row, rowIndex) {
    row.forEach(function(cell, colIndex) {
      if (cell) {
        Logger.log("URL from cell: " + cell);
        var comments = getCommentsFromDoc(cell);
        Logger.log("Comments to set in cell: " + comments);
        sheet.getRange(range.getRow() + rowIndex, range.getColumn() + colIndex + 1).setValue(comments);
      }
    });
  });
}

function GET_COMMENTS(url) {
  Logger.log("GET_COMMENTS called with URL: " + url);
  return getCommentsFromDoc(url);
}

function getDocAuthorFromUrl(url) {
  Logger.log("Starting getDocAuthorFromUrl with URL: " + url);
  if (!url) {
    Logger.log("URL is empty.");
    return "URL is empty.";
  }

  var docId = getIdFromUrl(url);
  Logger.log("Extracted docId: " + docId);
  if (!docId) {
    Logger.log("Invalid URL.");
    return "Invalid URL.";
  }

  try {
    var file = DriveApp.getFileById(docId);
    var owner = file.getOwner();
    var authorName = owner.getName();
    
    Logger.log("Author: " + authorName);
    return authorName;
  } catch (e) {
    Logger.log("Error in getDocAuthorFromUrl: " + e.message);
    return "Error: " + e.message;
  }
}

function showDocAuthor() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange();
  var cells = range.getValues();

  cells.forEach(function(row, rowIndex) {
    row.forEach(function(cell, colIndex) {
      if (cell) {
        Logger.log("URL from cell: " + cell);
        var author = getDocAuthorFromUrl(cell);
        Logger.log("Author to set in cell: " + author);
        sheet.getRange(range.getRow() + rowIndex, range.getColumn() + colIndex + 1).setValue(author);
      }
    });
  });
}

function GET_DOC_AUTHOR(url) {
  Logger.log("GET_DOC_AUTHOR called with URL: " + url);
  return getDocAuthorFromUrl(url);
}

function getWordCountFromDoc(url) {
  Logger.log("Starting getWordCountFromDoc with URL: " + url);
  if (!url) {
    Logger.log("URL is empty.");
    return "URL is empty.";
  }

  var docId = getIdFromUrl(url);
  Logger.log("Extracted docId: " + docId);
  if (!docId) {
    Logger.log("Invalid URL.");
    return "Invalid URL.";
  }

  try {
    var doc = DocumentApp.openById(docId);
    var body = doc.getBody();
    var text = body.getText();
    var wordCount = text.split(/\s+/).filter(function(word) {
      return word.length > 0;
    }).length;
    
    Logger.log("Word Count: " + wordCount);
    return wordCount;
  } catch (e) {
    Logger.log("Error in getWordCountFromDoc: " + e.message);
    return "Error: " + e.message;
  }
}

function showWordCount() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange();
  var cells = range.getValues();

  cells.forEach(function(row, rowIndex) {
    row.forEach(function(cell, colIndex) {
      if (cell) {
        Logger.log("URL from cell: " + cell);
        var wordCount = getWordCountFromDoc(cell);
        Logger.log("Word Count to set in cell: " + wordCount);
        sheet.getRange(range.getRow() + rowIndex, range.getColumn() + colIndex + 1).setValue(wordCount);
      }
    });
  });
}

function GET_WORD_COUNT(url) {
  Logger.log("GET_WORD_COUNT called with URL: " + url);
  return getWordCountFromDoc(url);
}

function extractUrls() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange();
  var cells = range.getValues();

  cells.forEach(function(row, rowIndex) {
    row.forEach(function(cell, colIndex) {
      if (cell) {
        var cellRange = sheet.getRange(range.getRow() + rowIndex, range.getColumn() + colIndex);
        var richText = cellRange.getRichTextValue();
        if (richText) {
          var url = richText.getLinkUrl();
          if (url) {
            Logger.log("Extracted URL: " + url);
            sheet.getRange(range.getRow() + rowIndex, range.getColumn() + colIndex + 1).setValue(url);
          } else {
            Logger.log("No URL found in cell.");
          }
        } else {
          Logger.log("No rich text found in cell.");
        }
      }
    });
  });
}