0

I am trying to get the date a row was modified inserted into a Modified Date column for that particular row using Office Scripts for Excel Online. This is the closest I can get since I haven't found scripts that that get just the modified row which will need to be dynamic. Details are in the comments in the code below:

function main(workbook: ExcelScript.Workbook) {
// This finds the location of the cell at K2 (Modified Date column is on K:K) but actually 
//want to get the modified row location.
 let datetimeRange = workbook.getWorksheet("Form1").getRange("K2");

// Get dynamic range of the worksheet. I'm not sure if this will work since there might be 
//some blanks. The number of rows and possibly columns will change so needs to be dynamic.
let myRange = 
workbook.getWorksheet("Form1").getRange("A1").getSurroundingRegion().getAddress();

// Get the current date and time with the JavaScript Date object.
let date = new Date(Date.now());

// This adds the date string to K2 but actually only want it added to Modified Date in the 
//Modified Date column (dynamic since columns can change) for only the row that was modified.
datetimeRange.setValue(date.toLocaleDateString() + ' ' + date.toLocaleTimeString());
 }

enter image description here

WixLove
  • 73
  • 1
  • 12
  • Question - is the modify event happening in the worksheet of via a Form external to the worksheet? Are you using Power Automate to run the script? – Jeffrey Nov 30 '21 at 23:48
  • The event is happening in the worksheet (a column in the worksheet will be modified) and yes, I will be using Power Automate to run the script. I should've mentioned that! – WixLove Dec 01 '21 at 18:37
  • There currently is no way to trigger a script run on cell/range change. Pure speculation - you may be able to construct a Power Automate Flow that triggers on the OneDrive file modified event. It would use scripts to get data into a Flow, do comparisons on rows and then write back a timestamp. The solution would be coarse, without time granularity. You would have to maintain a shadow copy of the workbook to do the comparisons. It's pretty convoluted. Would iterative calculation be better - see - https://www.spreadsheetweb.com/excel-iterative-calculation. – Jeffrey Dec 01 '21 at 21:33
  • Thank you, Jeffrey. Your ideas are appreciated and I'd have to look into the iterative calculation to see if that could work. However, I do have a Power Automate flow that already triggers when there's a change to the file (Sharepoint connector When an item or file is modified trigger). The next step to that flow is to run a script in Excel and that's where I'm stuck. I don't know how to identify the row that is modified in Excel so that the date can be entered into a column on that row. I am now looking into variables that would get the Active cell but haven't been able to get it to work yet. – WixLove Dec 02 '21 at 21:09
  • Cool. That sounds like an interesting solution! Unless I am missing something, it seems to me that the only way you'll be able to identify the edited row is by doing some sort of comparison with a shadow copy of the worksheet. I think the Office Script may know the active selection but there may be a delay before the script is run which would allow the user to have moved to another row or close the workbook. – Jeffrey Dec 03 '21 at 00:50

1 Answers1

0

I know this is an old thread, but for the others that may come here looking for a solution, this one is macro-less:

  1. Turn on iterative calculations for formulas (Options -> Formulas).
  2. Add this formula in K2: =IF(CELL("row")=ROW(K2), NOW(), K2)
  3. Drag that down your K column and you're done!
Tim
  • 1
  • 1