CODEWITHSUNDEEP
javascriptgoogle-apps-script
Lorenzo Pimps
Lorenzo Pimps

Reputation: 121

Google App Script - Script to write in cells according to the day of the week

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);
    }
  }

Upvotes: 0

Views: 674

Answers (2)

Cooper
Cooper

Reputation: 64032

Use getDay() method of Date() object like this:

sheet.getRange(1, new Date().getDay() + 19).setValue()

Upvotes: 0

Rupam
Rupam

Reputation: 1642

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:

  1. getDay() on MDN
  2. getRange()
  3. setValue()

Solution:

The following code will,

  • On Sunday, write the string "test" to cell S1,
  • On Monday, write the string "test" to cell S2 and so on.
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");
}

Upvotes: 1

Related Questions