+ Reply to Thread
Results 1 to 8 of 8

Automate conditional formatting using Apps Script

  1. #1
    Registered User
    Join Date
    02-24-2022
    Location
    Bangkok
    MS-Off Ver
    Excel 2016
    Posts
    15

    Automate conditional formatting using Apps Script

    Hi,

    I'm tracking the rankings for a list of keywords on a monthly basis.
    There is a script I'm using to
    - Create a new column
    - Copy paste the data from my current column to the newly created one

    However I have two issue I would like to solve:
    1. I have some conditional formatting running on column F (color to track the change in keywords ranks), however when my new column is being created, the conditional formatting is not updated and stick to the old column. Is there any way I can change this and automate it on Apps Script ?
    2. How can I automate the script so it runs by itself without any human interaction on the 1st of each month ?

    Link to Google sheet: https://docs.google.com/spreadsheets...Ye8/edit#gid=0

    My script:
    Please Login or Register  to view this content.
    Thanks a lot for your help in advance.
    Last edited by Sleurgh; 04-11-2022 at 11:51 PM.

  2. #2
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Automate conditional formatting using Apps Script

    First step is to cleanup the original CF rules.

    The GREY (if cell in range is empty) CF rule should be at the top, otherwise the green/red/white will take precedence.
    To explain... Google Sheets CF is opposite direction to excel... i guess you can say that it is "layered from bottom to top".
    Also, you dont need the "IF(xxxx,1,0) because CF is always based on IF, so it is redundant

    There should only be ONE (1) GREEN rule, here it is:
    Please Login or Register  to view this content.
    There should only be ONE (1) RED rule, here it is:
    Please Login or Register  to view this content.
    Then finishing with the WHITE rule:
    Please Login or Register  to view this content.
    Screen Shot 2022-04-12 at 5.27.24 pm.png

    Next is to implement the changes you want...


    There were a couple of issues with the apps script, such as setting the date to the wrong cell, and trying to get a date format that is not supported in the way it was scripted, and not the same as the what is wanted for the result on the sheet.

    All of this has been corrected, AND i have added in the resetting of CF rules. Heres the new script:
    Please Login or Register  to view this content.
    Save the script so it is locked in and ready to use.
    Screen Shot 2022-04-12 at 3.58.43 pm.png


    Lastly, set a trigger to automate this every month...

    In apps scripts, go the the clock icon (Triggers) on the left hand side
    In the bottom right hand corner, click the blue "+ Add Trigger" button
    Go to the dropdown below "Select event source"
    Change it to "Time-driven"
    Go to the dropdown below "Select type of time based trigger"
    Change it to "Month timer"
    Go to the dropdown below "Select day of month"
    Set it as you like (mostly likely this will be the 1st day of the month)
    Go to the dropdown below "Select time of day"
    Set it as you like (most likely you want this before start of work/business on the day)
    Scroll down and click the blue SAVE button
    Screen Shot 2022-04-12 at 3.59.59 pm.png


    Now you can return to the <> Editor on the right hand side menu.

    NOTE: i have implemented the CF rules, and the Apps Script for you... BUT i should not implement the Trigger for you otherwise you will not be able to access it (for example if you want to modify it, if i create the trigger then only i can modify the trigger that i create, so you must create it so that you can modify it later)
    Last edited by janmorris; 04-12-2022 at 06:29 AM.
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  3. #3
    Registered User
    Join Date
    02-24-2022
    Location
    Bangkok
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Automate conditional formatting using Apps Script

    Thanks a lot for your detail answer, that is really helpful!

    Couple of questions below:

    1) For the CF cleaning, everything is perfect except one small thing. If the cell doesn't displayed a number this month but had a number the previous month, the cell should be red as we lost a keyword (cell F8 should be red, not grey). I'm not really sure how to fix this except adding another color code.

    2) Thanks a lot for fixing the date and the implementation of new CF rules, that's exactly what I needed.
    However, I did not say it in my original post (very sorry about this) but column F contains a lot of formulas that need to remain from month to month.
    How can I get all the formulas to remain in column F? (similar to "const rules = sheet.getConditionalFormatRules()" but for formulas)

    3) Again, I appreciate your details on how to automate this on a daily basis.
    Is it possible to automate a trigger based on one particular cell? For example, I put in K16 a date and I'd like the trigger to happen based on the cell's date value.

    I'm resharing the file with you if needed : https://docs.google.com/spreadsheets...Ye8/edit#gid=0

  4. #4
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Automate conditional formatting using Apps Script

    to answer your questions before i get into the edits...

    1) yes thats not a problem, i will look into it

    2) yes that can be done with getFormulas() and setFormulas(formulas), i will look into it

    3) Triggers are the automation, so if you put a date into a cell (K16) and you wanted the existing script to run on that (modifiable) date, then you will need further scripting that would have to run every day to check if the date is "today" then continue to run the existing script. So, the answer is: yes it is possible.

  5. #5
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Automate conditional formatting using Apps Script

    1) To fix the CF, i propose the new Custom formulas (they provide more flexibility if there is any need to reset them from Apps Script using whenFormulaSatisfied where ISBLANK() is easier to implement)

    In the following order from top to bottom...

    Grey:
    Please Login or Register  to view this content.
    Red:
    Please Login or Register  to view this content.
    Green:
    Please Login or Register  to view this content.
    White:
    Please Login or Register  to view this content.
    2) Instead of clearing the range in column F, which would require to use getFormulas() and setFormulas(formulas), i have opted instead to remove that section of code (as it would be redundant). I have also made some other adjustments to make it easier for you to modify without breaking the script (ranges are declared instead of being input numerically as row and column start to row and column end)

    3) the date in the cell needs to have a static position so that Apps Script can find it. Due to the insertion of new column pushing the date cell across its location will change if kept on the same sheet, and so it should be on a different sheet where it will not be moved/shifted.

    Here is the new Apps Script:
    Please Login or Register  to view this content.
    NOTE:
    you will still need to create a Trigger, and set Select type of time based trigger to Day timer
    Last edited by janmorris; 04-12-2022 at 05:04 PM.

  6. #6
    Registered User
    Join Date
    02-24-2022
    Location
    Bangkok
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Automate conditional formatting using Apps Script

    Hi Jeanmorris,

    Thanks a ton for your help and so sorry for my very late reply.
    Conditional formatting works perfectly now and declaring range is much more intuitive than input the numerical value of the column.
    I now have multiple spreadsheets to add columns, mixing app scripts automatic triggers with Supermetrics ones.
    Again, very grateful for your help and time!

  7. #7
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Automate conditional formatting using Apps Script

    Its always good to hear positive news, even if at a later time, that a solution is working.

    Regarding the intuitiveness with the ranges... yeaaa.. that was due to a fleeting moment of clarity while i was rewriting (why i didn't think of it's sooner.. duh! haha)

    Have a great weekend!

  8. #8
    Registered User
    Join Date
    02-24-2022
    Location
    Bangkok
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Automate conditional formatting using Apps Script

    It's ok, there is so many possibilities in coding that sometimes you get overwhelmed and forget all the possibilities you have.
    At the end the scripting is working well and that what matters
    Have a great weekend too !

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. need a little change in google apps script for saving data permanently
    By akshay6s in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 8
    Last Post: 12-30-2021, 09:27 AM
  2. need a little change in google apps script for saving data permanently
    By akshay6s in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-30-2021, 08:07 AM
  3. Convert VBA to Google Apps Script
    By B_H in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2021, 04:39 AM
  4. VBA to Google Sheets Apps Script
    By Guitarist in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-08-2021, 11:45 AM
  5. VBA to Google Sheets Apps Script
    By Guitarist in forum Excel General
    Replies: 0
    Last Post: 07-08-2021, 11:45 AM
  6. Converting my VBA to Apps Script
    By Guitarist in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 05-21-2021, 10:34 AM
  7. [SOLVED] VB Script to replace Conditional formating
    By 2k05gt in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-14-2011, 09:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1