Extract and paste into cell from Show Edit History HTML container

[Best Bet Media]

Good Day experts.
I am not a very experienced person in this field but have been playing for over 4 days to get this right and am still hitting my head against the wall trying to find a solution.
I have read so many suggestions out there and all seem to have the same issue with security and privacy of google and user emails.

So my new thinking was to rather extract the data from the html container div.docs-blameview-author and div.docs-blameview-timestamp and paste that in row A.
This would happen after edit from any cell within a row.

Sadly I am not winning with this thinking even although I think my script is correct so am hopeful one of the experts can assist in where I am going wrong.
I do appreciate this is not the most stable approach due to google maybe changing their containers but for now I think it might be the cleanest and easiest way to provide the data I am trying to get.
user and date of last edit of a row.

Here is the script:

function onEdit(e) {

  var sheet = e.source.getActiveSheet();

  if (sheet.getName() !== 'Sheet1') {

    return;

  }

  var row = e.range.getRow();

  var rowIndex = row - 1;

  var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();

  var url = 'SPREADSHEET FULL URL'&overlay=false&showEditHistory=true&range=' + row;

 

  // Fetch the HTML content of the Show Edit History feature

  var response = UrlFetchApp.fetch(url, {

    headers: {

      Authorization: 'Bearer ' + ScriptApp.getOAuthToken()

    },

    muteHttpExceptions: true

  });

  if (response.getResponseCode() !== 200) {

    Logger.log('There was an error fetching the Show Edit History data.');

    return;

  }

  var html = response.getContentText();

 

  // Extract the editor and timestamp elements

  var editorIndex = html.indexOf('Edited by ') + 'Edited by '.length;

  var editorEndIndex = html.indexOf('\n', editorIndex);

  var editor = html.substring(editorIndex, editorEndIndex).trim();

 

  var timestampIndex = html.indexOf('Timestamp: ') + 'Timestamp: '.length;

  var timestampEndIndex = html.indexOf('\n', timestampIndex);

  var timestamp = html.substring(timestampIndex, timestampEndIndex).trim();

 

  // Write editor and timestamp to Column A and B of the edited row

  sheet.getRange(rowIndex+1, 1).setValue(editor);

  sheet.getRange(rowIndex+1, 2).setValue(timestamp);

I really do hope someone can assist in this as I am about to give up and just get users to continue to enter this is manually. (Not ideal but doable)
Thanking all in advance for any feedbackl and or suggestions.

[cor.va...@gmail.com]

Log every line/variable to check where it is not what you expected

[cwl...@gmail.com]

Apps script runs on the Google servers, and you are trying to do something at the browser level. It can't work. To interact with the browser, you would have to develop a browser extension similar to how Grammarly works. That is how it is able to interact with the Google products.

[Best Bet Media]

Thank you cwl... for your feedback.
I sort of gathered that this might be the issue.
Seems so annoying for such a simple function not be executed with endless challenges.
I assume the interact with browser comment relates to the html container holding this data to fetch.

At this stage I am about to give up on this specific function, and now seems out of my scope to try and figure out a fix and or create new extension just to have this to work.

So I also think my solution would be to have a user enter  their initials on a pop up on onEdit() and find a way to input that into a column.
The concern here is how to log this if multiple users are on the same sheet and know which user edited which row

[cwl...@gmail.com]

I am researching how you might be able to hack it using the Drive API for revisions. 
https://developers.google.com/drive/api/guides/change-overview

[cwl...@gmail.com]

Also check out: https://developers.google.com/drive/api/guides/manage-revisions

[cwl...@gmail.com]

I think the easiest way to track changes to a specific cell, would be to use onEdit() and onChange() triggers. Then check to see if that specific cell was edited. If yes, log the info somewhere (another spreadsheet if you are worried about someone editing the details).

[Emerson Maia]

try something like this:

function onEdit(e) {

  const sheet = e.source.getActiveSheet();

  if (sheet.getName() !== 'Sheet1') {

    return;

  }

  const row = e.range.getRow();

  const rowIndex = row - 1;

  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();

  const url = `SPREADSHEET FULL URL&overlay=false&showEditHistory=true&range=${row}`;

  // Fetch the HTML content of the Show Edit History feature

  const response = UrlFetchApp.fetch(url, {

    headers: {

      Authorization: `Bearer ${ScriptApp.getOAuthToken()}`

    },

    muteHttpExceptions: true

  });

  if (response.getResponseCode() !== 200) {

    Logger.log('There was an error fetching the Show Edit History data.');

    return;

  }

  const html = response.getContentText();

  // Extract the editor and timestamp elements

  const editor = html.match(/Edited by (.+?)\n/)?.[1]?.trim() || '';

  const timestamp = html.match(/Timestamp: (.+?)\n/)?.[1]?.trim() || '';

  // Write editor and timestamp to Column A and B of the edited row

  sheet.getRange(rowIndex + 1, 1).setValue(editor);

  sheet.getRange(rowIndex + 1, 2).setValue(timestamp);

}

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e858b6dd-727e-44f9-b0a7-e058803c7267n%40googlegroups.com.

[Best Bet Media]

Thank you Emerson, appreciate your time and thought given to a solution.
However sadly your suggestion did not work as expected.

I have now decided to take a different approach which seems to be working so far on initial testing.
That is to prompt end user for their initials on opening sheet, output this in a separate hidden sheet next to a list of users initials and make an X  next to initial.
Then on edit - refer back to this to produce an Initial and time stamp in edited row in sheet1.
I did try to use the " PropertiesService " function but that did not store the initials for some reason so reverted to separate hidden sheet.
Also to store the date and time of last edit in hidden sheet, on 1st time open by any user, check if current date is old date +1 or more , if it is, then clear the "x" and prompt for initial again.

Not the cleanest of logical thinking but I think this work around removes the frustration of trying to get user credentials through  " getEffectiveUser "  " getActiveUser "  " getEmail " or even trying to grab from the HTML selectors.

I am not quite finished with the script but holding thumbs this approach will be a trusted work around.

Thanks again 

[Best Bet Media]

Thank you again for you time and thought in trying to resolve my frustration of finding a workable solution.
I do appreciate your time and thought given.

I have decided to take a completely different approach explained in another reply in this thread.
Would love your feedback on tis thinking as I am not the most logical person and that often bites me back in long winded code.
I will post final script when done and tested and would love your feedback if a cleaner more robust way is possible.

[cwl...@gmail.com]

Yes, I think this is the best "server side" approach you can take. 

[cwl...@gmail.com]

p.s., if it helps any, I just have this utility script I plug in wherever I need it and call it when needed.

function validateUser() {

  return Session.getActiveUser().getEmail();
}

function amazingFunction() {

...

const user = validateUser()

...

sheet.appendRow(["ABC", user, "XYZ])// etc...

}