I'm trying to add this section to my Google app script. I want to add a script that changes the cell to write into, according to the day of the week. For example:
If day=monday then write in cell S1
If day=tuesday then write in cell T1
If day=wednsday then write in cell U1
ecc.
Do you know how can i achieve this result? Thanks!
EDIT: This works for me!
if (total != 'error') {
if(new Date().getDay() === 0){
sh3.getRange('S'+counter).setValue(total);
} else if(new Date().getDay() === 1){
sh3.getRange('T'+counter).setValue(total);
} else if(new Date().getDay() === 2){
sh3.getRange('U'+counter).setValue(total);
} else if(new Date().getDay() === 3){
sh3.getRange('V'+counter).setValue(total);
} else if(new Date().getDay() === 4){
sh3.getRange('W'+counter).setValue(total);
} else if(new Date().getDay() === 5){
sh3.getRange('X'+counter).setValue(total);
} else if(new Date().getDay() === 6){
sh3.getRange('Y'+counter).setValue(total);
}
}
In Javascript, you can get the day of the week (numeric index) using new Date().getDay() method.
The next part is to get a reference to the target cell on the spreadsheet using sheet.getRange() method.
I would recommend to go through the following resources to understand how the solution works:
The following code will,
const spreadsheetId = "...";
const sheetName = "Sheet1";
const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
const sheet = spreadsheet.getSheetByName(sheetName);
const today = new Date().getDay();
const cell = sheet.getRange(`S${today+1}`)
cell.setValue("test");
If you want to skip Sunday and write to S1 on Monday, you need to modify it
if (today > 0) {
const cell = sheet.getRange(`S${today}`)
cell.setValue("test");
}
sheet.getRange(1, new Date().getDay() + 19).setValue()