An IF statement condition based on response to another IF statement

Question

I have a tracking spreadsheet that opens up a user spreadsheet and tracks whether or not they have responded to the correct week. The user may sign up at any time, so i have an if statement that counts the number of days between today and spreadsheet creation date. It then moves it to the correct cell. I then need another if statement that says if the last update date is less than 7 days, then green if not red. Both If statements work as standalone code, but when I try to next them, the 2nd If statement is running first, putting Y or No into the cell and then it activates the correct cell based on the spreadsheet date! I need that 2nd If statement to run after it has activated the correct sheet.

I have tried moving the syntax. I have tried creating the If statement inside each If statement. The problem is going to be that the code needs to run automatically for the full 52 weeks of the year

function trackingSheetUpdate() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName('Active Campaign');
var trackSheet = ss.getSheetByName('Tracking')
var now = new Date()
var createDate = sourceSheet.getRange(2, 6).getValue()
var NoOfDays = (now - createDate) / (1000 * 60 * 60 * 24);
var diff = Math.round(NoOfDays)
Logger.log(diff);

var rowData = sourceSheet.getRange(1, 1, sourceSheet.getLastRow(), sourceSheet.getLastColumn()).getValues();

for (var i = 2; i < rowData.length; i++) {

    var getUpdate = sourceSheet.getRange(i, 5).getValue();
    var openSheet = SpreadsheetApp.openByUrl(getUpdate).getSheetByName('Sheet1')
    var row = openSheet.getLastRow()
    var weeklyUpdate = openSheet.getRange(row, 1).getValue();
    var calc = (now - weeklyUpdate) / (1000 * 60 * 60 * 24)
    var calcDate = Math.round(calc)

    if (diff > 1 && diff <= 7) {
        trackSheet.getRange(i, 2).activateAsCurrentCell()
    } else if (diff > 7 && diff <= 14) {
        trackSheet.getRange(i, 3).activateAsCurrentCell()
    } else if (diff > 14 && diff <= 21) {
        trackSheet.getRange(i, 4).activateAsCurrentCell()
    }


    if (calcDate <= 7) {
        trackSheet.getActiveCell().setValue("Y")
    } else {
        trackSheet.getActiveCell().setValue("N")
    }

I have set the creation date to 8/5/19, so it should go to column 2 - which it does. I have set the last update date to 8/5/19, so it should be red and No, which it is (except it puts this result into whichever cell I leave it in, then moves to column 2 and activates it!)

score 0 · Accepted Answer · answered May 20 '19 at 01:20

Please try to reformat your question so that it is clearer what you are expecting to happen, what you want to happen, and what precisely your question is.

From what I understand, you're looking for:

A DRY solution to get the current week.

You can do this a couple ways. Using your existing diff variable, instead of checking if it's > && <=, you can use modulo and division, e.g.

var diff = 7
console.log((diff-(diff%7))/7 + 1)

var diff = 22
console.log((diff-(diff%7))/7 + 1)

var diff = 45
console.log((diff-(diff%7))/7 + 1)

You could also use something like a getWeek() function to calculate the week in the year, and offset it to what your spreadsheet needs, or the like.

Your first if statement sets the Current Cell, and your second if statement tries to set a value to the Active Cell. This doesn't work how you want.

First, note that getActiveCell() and getCurrentCell() work differently. You might try using getCurrentCell() in your second if statement--however, it is probably preferably to get the range using your first if statement, and set it using the second, e.g.,

var weeknum = (diff-(diff%7))/7 + 1;
var myCell =  trackSheet.getRange(i, weeknum)

calcDate <= 7 ? myCell.setValue("Y") : myCell.setValue("N")

Thank you.. the problem was the difference between active and current cell.. so changing that worked a treat! With regards to the DRY solution you've suggested, this is something new to me... but I'm going to have a play with it.. the better the code the faster it is .. right... Thanks again :) — Jacky, May 20 '19 at 08:41
@Jacky While yes, the math should be faster to process than running comparisons for every single week in a year, the bigger advantage is being much easier to read, maintain and adapt your code as you need in the future. — sinaraheneba, May 21 '19 at 01:40

An IF statement condition based on response to another IF statement

1 Answers1