Google-sheets – Remember each user’s last cell cursor location

formulasgoogle sheetsgoogle-apps-script

Is it possible to have Google Sheets remember each user's either last cursor location or last edited cell or preferably the value of the cell in Row2 that matches the current date? (In Row1 I have the day of the week that corresponds to the date that is in the same column but in Row2.) And then when each user opens the spreadsheet, their cursor will automatically go to that saved location. Is this possible?

Best Answer

To make Google Sheets "remember" something like the last cell edited by each user first you could use a on edit or on change Google Apps Script triggers to get the cell address.

To make Google "remember" the value of a cell, as the value could be volatile, then you should use a Google Apps Script time-driven trigger or if you only require the last value shown to a user, then you could use an HTML Service dialog or sidebar and client-side code to poll de cell value every second or something like that to get the value.

Once your script has the cell address or value, your script could store it somewhere like a sheet in the same spreadsheet, the Properties Service as a script, document or user properties or an external resource.

Related Solutions

Google Sheets Timestamp – Automatically Set ‘Last Updated’ Cell

You can try adding a Google Apps Script to capture when a cell is edited and add a timestamp to a different cell. Here's a previous answer that is similar: Google Spreadsheet Timestamp?

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
   var r = s.getActiveCell();
   if( r.getColumn() == 13 ) { //checks the column
     var nextCell = r.offset(0, 1);
     if( nextCell.getValue() === '' ) //is empty?
       var time = new Date();
       time = Utilities.formatDate(time, "GMT", "HH:mm:ss");
       nextCell.setValue(time);
   };
 };
}

Google Sheets – Arrayformula with Dates Before and After

I wrote a custom function in Google Apps Script that takes all the criteria you want it to have (and more) in consideration.

Code

function myPayments(paymentPlan, dateRange) {
  var output = [];  
  for(var i=0, iLen=dateRange.length; i<iLen; i++) {
    var checkDate = new Date(dateRange[i]);
    var weekDay = checkDate.getDay()+1, sum=0;
    for(var j=0, jLen=paymentPlan.length; j<jLen; j++) {
      if(paymentPlan[j][1] == weekDay) {   
        if(paymentPlan[j][2] === "" && paymentPlan[j][3] === "") {
          sum += paymentPlan[j][0];
        } else if(paymentPlan[j][2] === "") {
          if(checkDate <= new Date(paymentPlan[j][3])) {
            sum += paymentPlan[j][0];
          }
        } else if(paymentPlan[j][3] === "") {
          if(checkDate >= new Date(paymentPlan[j][2])) {
            sum += paymentPlan[j][0];
          }
        } else if(paymentPlan[j][2] !== "" && paymentPlan[j][3] !== "") {
          if(checkDate >= new Date(paymentPlan[j][2]) && 
            checkDate <= new Date(paymentPlan[j][3])) {
            sum += paymentPlan[j][0];
          }
        } 
      }
    }
    if(sum == 0) {
      output.push([]);
    } else {
      output.push([sum]);
    }
  } 
  return output;
}

Screenshot

enter image description here

Remarks

If you extend the paymentPlan range, to contain the rest of the data as presented in the PAYMENT PLAN sheet, then you will notice that overlapping payments will be summed.

Add the script under Tools>Script editor and press the save button (in your case it's already there).

Example

I've added the solution in your example file.