Script for GoogleSheets that inserts date, time, and user names of everyone entering text in a specified cell?

Posted on

Script for GoogleSheets that inserts date, time, and user names of everyone entering text in a specified cell? – Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula or script parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common script or formula parse errors in Google Sheets, like Script for GoogleSheets that inserts date, time, and user names of everyone entering text in a specified cell?, and how to fix it.

I wanted to ask for your help in building a script in Google spreadsheet that would allow users to respond to comments left by different users in the same cell (cell where the initial comment was originally posted), while having this script insert a [date, time, user name] stamp in the beginning of currently written comment. Visually the end result of that one cell would ideally look like this:

[12/07/17, 11:15am, John] has this project been submitted? 
[12/07/17, 11:19am, Steve] currently finalizing it. Will be ready in 10 minutes 
[12/07/17, 11:22am, John] thanks for the update.

A script is currently floating around which is similar, but it is very limited – a slightly edited version is in the linked spreadsheet. This spreadsheet also contains a visual example and an explanation of what I’m trying to achieve. Please take a look and let me know if any further clarification is needed.

https://docs.google.com/spreadsheets/d/1tzVifer8KUNNoTXKq2W_-CCGzpD7EzIetTW5w_3EoSc/edit?usp=sharing

function onOpen() {
    SpreadsheetApp.getUi().createMenu('Allow Script?')
        .addItem('Yes', 'createSpreadsheetEditTrigger')
        .addToUi()
}

function createSpreadsheetEditTrigger() {
    checkTriggers();
    ScriptApp.newTrigger('runOnEdit')
        .forSpreadsheet(SpreadsheetApp.getActive())
        .onEdit()
        .create();
    var user = Session.getActiveUser().getEmail();
    SpreadsheetApp.getUi().alert('script is now authorized by ' + user + '');
}

function runOnEdit(e) {
    if (e.source.getActiveSheet().getName() !== 'Sheet1' || e.range.columnStart !== 1 || !e.value) return;
    e.range.offset(0, 1, 1, 2).setValues([
        [Session.getActiveUser().getEmail().split("@")[0],new Date()]
    ]);
}

function checkTriggers() {
    var allTriggers = ScriptApp.getProjectTriggers();
    if (allTriggers.length > 0) {
        for (var i = 0; i < allTriggers.length; i++) {
            ScriptApp.deleteTrigger(allTriggers[i]);
        }
    }
}

Answer :

I suppose the easiest would be appending a newline and the text.

function myFunction() {
  var worksheet = SpreadsheetApp.getActiveSheet();
  var placeholder = worksheet.getRange(1,1).getValue();
  var text = "1234";
  worksheet.getRange(1,1).setValue(placeholder + "n" + text);
}

A1 before:

This is a test

A1 after:

This is a test 
1234

Leave a Reply

Your email address will not be published.