to explain better, I have the id column and the data column: enter image description here
I would like to know if there is a way to calculate the difference in column dates using this patient id as a reference.
example: if id = 1, calculate the difference of all dates in the date column. so it will looks something like this: enter image description here
I'm guessing you are trying to get the first date of each patient ID as default value. And calculate the date diff of two date and append to 3rd columns as date different.
const sheet = SpreadsheetApp.getActive();
const sheet_values = sheet.getDataRange().getValues();
const ids = [...new Set(sheet_values.map(r => r[0]))]
const diff_arr = [];
for (i in ids){
const mathedvalues= sheet_values.filter(el => {return ids[i] == el[0]});
const firstdate = new Date(mathedvalues[0][1]);
mathedvalues.forEach(r => {
const date_diff = Math.abs(new Date(r[1]).getTime() - firstdate.getTime()) / (24 * 60 * 60 * 1000);
diff_arr.push(date_diff)
})
}
sheet_values.forEach((r,index)=>{
r.push(diff_arr[index])
})
console.log(sheet_values)
//if you want to set the value to the sheet, you can use setValue function
This might help you.
