Check Box in Column M if same row in Column L is not empty

[ZPL]

Greetings!

Our objective is to read a particular column in a given Google spreadsheet. In this case, the L column. If column L isn't blank, we want to check the checkbox in the cell directly to the right of the currently detected non-empty cell, in column M. I feel like if I could retrieve the row number from the actively detected non-empty cell in column L, then I could use that number to interact with the correct row of column M. Any ideas?

Thank you very much for your time!

var data = SpreadsheetApp.getActiveSpreadsheet().getRange("L:L").getValues();

for (i = 1; i < data.length - 1; i++) {

if (data[i] != "") {

???

}

}

[Laurie Nason]

Hi Zach,

Is there a reason you are not just using a formula on column M in the cell to check the box? e.g. for Row5 the formula for the checkbox column (M) would read =IF(L5<>"",TRUE,FALSE) - and you could probably do it in an ARRAYFORMULA (although I haven't had a whole lot of success with checkboxes and arrayformulas)

If you really need to do it in code, then you can get the range as you have and get the range for the next column

(untested...!)

var mysheet=SpreadsheetApp.getActiveSpreadsheet();

var data = mysheet.getRange("L:L").getValues();

for (i = 1; i < data.length - 1; i++) {

if (data[i][0] != "") {

//do the checkbox thing here

mysheet.getRange(i,13).setValue(true);

}

}

HOWEVER, if you are doing this a lot i.e. on edit trigger, you might want to limit it to only the row that is being changed - check the row number of the ActiveCell

Also, if you are doing this on a very large number of rows at once - you probably want to create an array for the whole column M and use the array to setValues() on it rather than doing individual updates per row

Laurie

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/10c76036-2e4c-4c2f-9738-d99580fe1a4cn%40googlegroups.com.

[ZPL]

Greetings, Laurie! Thank you so much for this help so far.

The context behind this system is that an external user will be dropping off tracking numbers into column L. They will then run a function from a button or a menu created for this. That manually triggered function will ideally deliver customers a shipping confirmation email with that provided tracking number. Once that shipping confirmation email is sent, the system would check the box on the spreadsheet. The header of the checkbox column is "Shipping Confirmation Sent".

The system would ideally retrieve the relevant customer data from the active row to use in the shipping confirmation email, such as which email the confirmation should be sent to. Right now, I've got it set up as shown below. I've only been able to get it to affect the header row (it changed the header of the Checkbox column to "TRUE") when I run it with "if (data[i] != "0")"

Anything besides that, and it appears to skip over the IF function altogether. I'm assuming I'm not understanding something about this correctly. I'm better with words than maths.

var data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders").getRange("L:L").getValues();

for (i = 1; i < data.length - 1; i++) {

if (data[i] != 'Shipping Confirmation Sent' && data[i] != '') {

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders").getRange(i, 13).setValue(true);

var retreivedEmail = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders").getRange(i, 2).getValue();

Logger.log(retreivedEmail);

var company = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders").getRange(i, 3).getValue();

var contactName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders").getRange(i, 4).getValue();

var street = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders").getRange(i, 5).getValue();

var city = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders").getRange(i, 6).getValue();

var state = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders").getRange(i, 7).getValue();

var postalCode = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders").getRange(i, 8).getValue();

var phoneNumber = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders").getRange(i, 9).getValue();

var qty = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders").getRange(i, 10).getValue();

var trackingNumber = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders").getRange(i, 14).getValue();

var addressForEmail = company + "\nAttn: " + contactName + "\n" + street + "\n" + city + ", " + state + " " + postalCode + "\n" + phoneNumber;

}

}

[Laurie Nason]

Ah - OK - I see what you are doing. 

I think that if you check your variables in debug mode you'll find that your data is a 2 dimensional array, so you may have to add in [0] to get the first (and only!) value for each row. 

I would probably too in addition to what you are doing:

• Only check the box (rather than at the beginning of your function) - do it as the last thing - that way if your function fails for any reason, you can still send the email easily enough by fixing the issue and running it again. 
• You might want to think about putting the time/date that the email was sent to the customer in another column too.
• I have taken to adding a simple tab on the sheet to store log entries of what has happened - so I log emails sent with addressees, the text of the email and the time/date it was sent, just so I can go back and look at what should have happened and didn't! (happens a lot to me ;-))
• And just to tidy up your sheet and make it a bit more readable - I would probably create a variable outside your "for loop" - var ordersSheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders")

Hope this helps!

Laurie

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7f86c1a1-54ea-4028-bb4c-a2dd9cfc84c2n%40googlegroups.com.