I have the following code:

function myFunction() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet(); // current spreadsheet
  var old_sheet = ss.getActiveSheet();                   // current (old) sheet
  var new_sheet = old_sheet.copyTo(ss);                  // make a copy
  SpreadsheetApp.flush()
  new_sheet.setName(Utilities.formatDate(new Date(), 'GMT-7:00', 'w')); // set the name
  new_sheet.getRange('A3').setValue(new Date(), 'GMT-7:00', 'w');         // set the cell A3
  new_sheet.getRange('B14:H18').clear({contentsOnly: true}); // clears content in B14:H18
  old_sheet.hideSheet();                                 // hide the old sheet
}

The part I am having trouble with is the following:

new_sheet.getRange('A3').setValue(new Date(), 'GMT-7:00', 'w');         // set the cell A3

My objective is to return the Sunday date into cell A2 OR return the current week number (starting sunday) into cell A3.

For example: 10/31/2021 would be put into A2 or 45 (week number) would be put into A3, from there I can work in either direction using regular formulas in google sheets.

I tried to setvalue(new date() --> using 'w' but ran into an error:

"Exception: The parameters ((class),String,String) don't match the method signature for SpreadsheetApp.Range.setValue."

Which I read here: How do you resolve a "The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues" error

Meaning a range is 2 dimensional, and I tried to use .push() but honestly I am not sure how to do it.

I know if I setvalue(new date()) will give me the current date, but I am not sure how to get the date that starts at the beginning of the week (sunday).

Thank you, if I misspelled or didn't explain correctly do let me know. I'm not native in English.

CodePudding user response：

Sunday date in cell A2

  var d = new Date()
  d.setDate (d.getDate() - d.getDay())
  new_sheet.getRange('A2').setValue(d);

CodePudding user response：

Here is the way to get a date of the last sunday:

function get_last_sunday() {
  var today = new Date();
  var day = today.getDay(); // 0 - Sunday, 1 - Monday, 2 - Tuesday, etc...
  var sunday = new Date(today.getTime() - 24*60*60*1000*day);
  return sunday;
}

console.log(get_last_sunday())

<iframe name="sif1" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

Probably you can use the formula this way:

new_sheet.getRange('A2').setValue(get_last_sunday());

To get week number you can use this function:

function get_week() {
  return Utilities.formatDate(new Date(), 'GMT-7:00', 'w');
}

And respectively:

new_sheet.getRange('A3').setValue(get_week());

As for the rest I don't understand your goal. Especially the 'OR' condition.