-1

I need a Button that deletes the entry of the first column(A:A) and creates a new one(H:H) and the Date at cell H2 to automatically set to the next Thursday or Sunday.

Currently, I just recorded a macro to copy B2:H6 and paste it to A2 and manually enter everything on column H and assigned the macro to a Button. Help is greatly appreciated. Thank you!

Example:: From this: 1 To this: 2

  • 1
    What attempt have you made to solve this other than the macro that you wrote? – Tedinoz Sep 4 at 6:30
  • I was initially put off by what appeared to be the complexity of this question; however when one broke it down into segments of work, the path to a solution became more clear. I mention this because even if the coding was too complicated for you, there is still the opportunity for you to think about the components of work and their logic. To have at least thought through how the question might be approached is the first step towards developing coding skills. – Tedinoz Sep 5 at 22:16
1

The following is offered on the basis that there are usually many ways to solve a problem. This is just one approach.

There are three elements to the resolution of this question:
1 - update the data in columns columns A-to-G
This is achieved through a virtual "copy/paste" using copyValuesToRange(sheet, column, columnEnd, row, rowEnd) doc ref.

2 - set the checkboxes in Column H to false
This is a conventional approach; get the range and array values; create a loop to change the array values; and reset the array values.

3 - update the date in cell H2
The Javascript method Date.getDay() works here (it is the basis for the Google Sheets built-in function WEEKDAY(); the only difference being that Javascript is zero-based on Sunday and, under Google Sunday = 1). Two IF statements (switch was an option) evaluate for Sunday or Thursday and provide for the appropriate adjustment.


function wa13333403() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "wa133334";
  var sheet = ss.getSheetByName(sheetname);

  // update dates - column A to G
  var sourcedaterange = sheet.getRange("B2:H6");
  // This copies the data in B2:H6 in the source sheet to A2:G6 in the second sheet
  // copyValuesToRange(sheet, column, columnEnd, row, rowEnd)
  sourcedaterange.copyValuesToRange(sheet, 1, 7, 2, 6);

  //update data values for Column H
  var colH = sheet.getRange("H3:H6");
  var colHdata = colH.getValues();
  for (i = 0; i < 4; i++) {
    colHdata[i][0] = false;
  }
  colH.setValues(colHdata);

  // update Column H date
  var targetdate = sheet.getRange("H2");
  var datevalue = targetdate.getValue();
  var dayvalue = datevalue.getDay();
  var date = new Date(datevalue);
  //Logger.log("DEBUG: date = "+datevalue+", day of the week = "+dayvalue);
  if (dayvalue == 0) {
    // Day is Sunday so add 4 days to create new date for following Thursday
    var newdate = new Date(date.setDate(date.getDate() + 4))
    //Logger.log("DEBUG: The  day is a Sunday so, targetdate = "+newdate);
  }
  if (dayvalue == 4) {
    // Day is Thursday so add 3 days to create new date for following Sunday
    var newdate = new Date(date.setDate(date.getDate() + 3))
    //Logger.log("DEBUG: The  day is a Thursday so, targetdate = "+newdate);
  }
  targetdate.setValue(newdate);

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.