google-spreadsheet,gs-conditional-formatting , using AND/OR operators together


using AND/OR operators together

Question:

Tag: google-spreadsheet,gs-conditional-formatting

I'm trying to use conditional formatting to color a row if the following is true: column E is less than 50 days old AND if column F does not contain the word exempt OR NA.

So initially i tried =OR(AND($E$4:$E>TODAY()-50, $F$4:$F <> "Exempt"), AND($E$4:$E>TODAY()-50, $F$4:$F <> "NA")) but that did not work. Can anyone steer me the right way?


Answer:

With your range selected (starting in the 4th row), try this formula.

=AND($E4>TODAY()-50, AND($F4 <> "Exempt", $F4 <> "NA"))

You only need to make the first row resolve as TRUE. With the relative cell row addresses, the formula will adjust for rows further down. You can put that formula into an unused cell on the 4th row and fill down to check the results.


Related:


Google Sheets Adds-on open automatically when user opens a sheet or clicks a link in worksheet


google-apps-script,google-spreadsheet
I created an google sheets add-on. However, it is a bit hidden in the adds-on menu. Is there a way to enable the Adds-on automatically when user opens a sheet or when user clicks a link in a sheet? I searched on Google Sheets documentation but found nothing. EDIT1: Since...

how i create a formula of payment using weeknum formula


google-spreadsheet,week-number
i need to calculate paymant of this people in the same week like you see in this spreadsheet https://docs.google.com/spreadsheets/d/1fHEtOFxZeRjJ07duCxxhUwUL_1G3BUrvKRMYjT9_OmM/edit#gid=91744412 I used WEEKNUM formula to extract the number of week using the data in column A ..for example "anselmi" earned 16 in week 24...... thanks

Add 5 Business Days to a timestamp


google-spreadsheet
I have the following code which we have used to note the start date of projects. We have found it to be less than useful. I would like to change this to a due date by adding 5 business days to the current date. function onEdit() { var s =...

Javascript (Google Scripts) value from a function is not returning


javascript,google-apps-script,google-spreadsheet,google-calendar
I think could be a simple one to solve, I am stuck working this simple issue out. I have called a createEvent function to create a google calendar event. As part of this function I also get the google calendar event ID as EventId and want to return it. For...

Nslookup or dig in Google App Script


google-apps-script,google-spreadsheet,nslookup,dig
Is there any way to run an nslookup / dig in Google App Script? I m looking to get the results of this command in a Google Spreadsheet. Thanks...

Google Form to Document Merge to Pdf to Mail


google-apps-script,google-spreadsheet
I think my issue is in the e.values I am using along with the ('keyXXXXX', XXXXX); that I am using. The original code I used was something I borrowed from TJ Houston's site. I am using it for an application process. I wrote the code following the process of using...

Google Sheets API for clickable images in Google Sheets


google-apps-script,google-spreadsheet,google-spreadsheet-api,google-spreadsheet-addons
In Google sheets, you can insert a clickable image to a cell which links to a function in app script. Can we also insert this clickable image and link it to app script from our server using Google Spreadsheet API? Or Can we call app script from our server? https://developers.google.com/apps-script/guides/menus#clickable_images_and_drawings_in_google_sheets...

Edit a Google Spreadsheet using Google Apps Script


google-apps-script,google-spreadsheet
I am trying to edit a google spreadsheet using google apps script. Basically, I have a cell in the spreadsheet that has the value "Pending Approval" in it. When the script is executed, I want it to change the value in that cell to "Approved". Is there a simple way...

Google Spreadsheet setBackgroundColors() “Deprecated”


google-apps-script,google-spreadsheet
A gs I have been using in the past to check for duplicates, is no longer working. The script would check all cells in the spread sheet if any were identical it would highlight all their occurrences green. I also had another function that would revert all the cells back...

How can I exclude a sheet in an array, and sort with a range


google-apps-script,google-spreadsheet
Good Morning everyone I have a code that was posted here a while back, I recently started using for a google spreadsheet and tweaked it a bit, how ever there are 2 thigs I have not been able to accomplish the first thing is to Exclude the Master sheet from...

Conditionally formatting multiple rows with the same conditions


google-spreadsheet
I am looking to format 500+ rows of data and essentially, G5-K5 (in every row) needs to be highlighted a light color (any color) if J5 equals "Regular". This same condition will exist in every row, so it should be simple - but I am not an excel master by...

How to get the last entry containing a certain value in Google Spreadsheet


google-apps-script,google-spreadsheet,google-form,formulas
I have an answer spreadsheet linked to a Google form. I wish to get the last date that and answer was submitted. Lets say I my entries look like this : DATA : A B 1 Date String 2 2015-05-09 Abb 3 2015-05-11 Bcc 4 2015-05-12 Cdd 5 2015-05-20 Bcc...

Prevent users from creating new sheets in a shared google spreadsheet


google-spreadsheet,google-docs
I have created a spreadsheet with all sheets protected from editing except for a single cell, where the user is supposed to enter a search value, which filters the sheet. I shared the spreadsheet with enabled editing to allow for that, but that also enables users to create new sheets,...

How to get Google Sheet data into a HTML page using jQuery/Javascript?


javascript,jquery,html,google-spreadsheet,google-spreadsheet-api
Is there a way to obtain Google spreadsheets data into a HTML page using jQuery/Javascript? I couldn't find any examples/resources regarding the same on the Google sheets v3.0 API page. Could anyone help? I have tried to get the sheets feed using the following request GET https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full...

Extract matching data from varying number of row


google-spreadsheet,spreadsheet,pivot-table
I am trying to build graphic indicators from a (potentially) large set of data using google-spreadsheet. So far, I've used a pivot table to extract the information from the raw data, and I want to build intermediate tables to calculate the different values I need for my indicators. So far,...

Change/replace part of an URL contained WITHIN a function


javascript,replace,google-spreadsheet
Not sure if this is even possible. But ideally, I'd like visitors to my site to be able to type a unique identification number in an input field, click submit, and have another script replace the Google sheet reference ("DATA1") with their own ID number. So, effectively change the queried...

Yearly quarters formula


google-spreadsheet,analytics
In my Google Spreadsheet I need to display the current quarter of the year for my analytics report.

Access spreadsheet via Google API invalid_grant


java,google-api,google-spreadsheet,gmail-api
I have created a google form, and keeping the form result in an excell sheet named yht_istekler. I am using following code snippet. taken from Create Spreadsheet using Google Spreadsheet API in Google drive in Java Though I am getting following error, what am I doing wrong? List<TrenSeferScheduleEvent> trenSeferScheduleEventList =...

Google sheet app script errors after laptop sleep


google-apps-script,google-spreadsheet
I have a custom function in a sheet that calls a mysql database. It extracts one value from mysql per row, e.g. | A | B -------------------------------- 1 | YES | =readCompleteFromMember(A1) 2 | NO | =readCompleteFromMember(A2) If I let my laptop go to sleep and open it the next...

Increment count in column based on value in column


excel,google-spreadsheet,excel-formula,array-formulas
I've 2 columns A and B. A contains names and B contains the count of those names. Ex: let A1 have value "fruits". Then B1 must have value 1 because this is first time "fruits" is present in A. Next A2 has "flowers", and B2 will have 1 against "flowers"....

How to upload a data frame into a Google Spreadsheet


r,google-spreadsheet
I would like to upload a data frame into one particular workingsheet within a spreadsheet on Google Spreadsheet using: googlesheets library https://github.com/jennybc/googlesheets Usually, its possible to upload a whole spreadsheet using the: upload_ss("data.xlsx", sheet_title = "Year", verbose = TRUE) or individual sheets with either Year <- Year %>% edit_cells(input =...

Google Apps Script: event.setTime error and time format


google-apps-script,google-spreadsheet,google-calendar
I'm creating something of a booking system which is nearly complete. Currently I'm collecting data from a form, passing it to a sheet, and then using some of that info to create a calendar event. This all works fine. On creating the event, I'm also collecting the eventID so that...

Google Apps Script: Bi-directional sync when rows are deleted


google-apps-script,google-spreadsheet
I have two sheets which are synced in Google Sheets using a GAS i found on this SO, which i put on a change trigger Google Apps Script: Is there a way to keep two sheets synchronized? script is here var sourceSpreadsheetID = "ID HERE"; var sourceWorksheetName = "SHEET NAME...

Can I add a specific sentence to a cell based off of another cells answer?


google-apps-script,google-spreadsheet
I have a script that will move rows based off of an answer. Can I add update a cell with a static sentence based off of another cells answer? For example, if X1 in Spreadsheet1 is"Yes", then it adds the sentence "Your job was successful." to cell F1 in Spreadsheet2?...

Calc/Spreadsheet: combine/assign/migrate cols


excel,google-spreadsheet,openoffice-calc
Sorry, i can't use the right terms but i try to explain my task: In Calc, or Spreadsheet I have two worksheets with columns like this: | ID| | 32| | 51| | 51| | 63| | 70| and | ID|Name | | 01|name1 | | 02|name2 | ... |...

Auto populate fields in Google Sheets


google-spreadsheet
I am creating a burndown chart based in Google Sheets based on ticket info from Jira. What I have is the begindate and the enddate of a project in a cell. What I want is to have a chart with every date during the running of the project and the...

Automatically export incoming emails from Outlook to existing Google spreadsheet [closed]


email,outlook,google-spreadsheet,export
Is it possible at all to automatically export incoming emails from Outlook to existing Google spreadsheet (and be able to edit the spreadsheet, insert new data)? If it is so, where would you start? We want to manage our inbox this way and keep track of assignments. Thank you!...

SpreadsheetApp.getActiveSpreadsheet() is breaking script


google-apps-script,google-spreadsheet
I'm writing my 1st google app script. Spreadsheet opens a sidebar in onOpen(). Sidebar has Button and in SidebarJavaScript.html I have listener to that button. I'm calling SpreadsheetApp.getActiveSpreadsheet() inside listener and after this line script is not executing. What could possibly be wrong here? function onScrapeClick(){ // Disable Button this.disabled...

Output Google Spreadsheet to XML/RSS/Atom using Google AppScript Content Service


xml,google-apps-script,rss,google-spreadsheet,google-docs
So I've searched this site and found nothing really useful so far to accomplish this. I am wanting to turn a spreadsheet into an RSS feed using google script content service. How do I declare column values (i.e. pubDate, Author, Content) so it will output correct RSS/Atom feed. Following their...

How to implement rangeProtect() over multiple tabs - google spreadsheet


function,google-apps-script,google-spreadsheet,spreadsheet
I'd like to implement a script to implement range protections on the different tabs of the spreadsheet when someone create a new spreadsheet by copying my template. The issue I encounter is when the script run through the protection implementation. My code seems to be fine as per the debug...

Function to check for first blank cell


javascript,loops,google-apps-script,google-spreadsheet
so, I'm attempting to define a custom function that returns the row number of the first blank cell it finds within a custom range. function checkForBlank(startRow,startCol) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var checkRow = startRow; var checkCol = startCol; var range = sheet.getRange(checkRow, checkCol); var i...

Counting values embedded in strings inside a column (Google Spreadsheets)


excel,google-spreadsheet,excel-formula,formula,countif
I have a Google Survey where I created some multiple choice questions. Now, I am trying to count the responses. [A] [B] [Response#] [Selections] [1] [Apple,Orange,Banana] [2] [Orange,Banana] [3] [Apple,Orange,Banana] [4] [Banana] [5] [Apple,Banana] [6] [Apple,Orange] . So on my summary spreadsheet, I would like to have the totals: [Favorite...

Convert from base 10 to base 3


google-spreadsheet,base
I have a whole column in Google sheets with the numbers 1-400. Is there an easy way to convert these numbers to base 3?

access drive with access token


php,google-drive-sdk,google-spreadsheet,google-oauth
struggling to understand the oauth2 token and refresh token processes ive got this code $url = 'https://www.googleapis.com/oauth2/v3/token'; $data = array('client_id' => 'clientid', 'client_secret' => 'secret','refresh_token' => 'token','grant_type' => 'refresh_token'); $options = array( 'http' => array( 'header' => "Content-type: application/x-www-form-urlencoded", 'method' => 'POST', 'approval_prompt'=>'force', 'access_type'=>'offline', 'content' => http_build_query($data), ), ); $context...

How do I stop google sheets reassigning formulae values on another sheet


google-spreadsheet
My issue is as follows: I have formsite automatically adding rows into a workbook in Google Sheets. Another sheet formats the data for some data capturers to just do a copy and paste into another programme. My issue is that the mapping seems to go haywire every time formsite adds...

Can I associate a user's account with an action to the drive API?


javascript,oauth,google-drive-sdk,google-spreadsheet,google-oauth
We're looking to make a little webapp to manage our week-long nerf war (humans vs zombies to be precise), and we're thinking about how easy it would be to have Google Sheets be our only backend, and our frontend be entirely javascript/html/css. Let's say there's two actions that can be...

client error (400) using gsheet2tbl to get Google Sheet data


r,google-spreadsheet
Since Rcurl no longer works for importing data into R from Google Sheets, I have been using gsheet2tbl. This has been working well but today I was trying to download from a recently created Google Sheet and I received the following error: url2<-"https://docs.google.com/spreadsheets/d/.../edit?usp=sharing" d <- gsheet2tbl(url2, sheetid = 0) Error...

If Statement Not Working on Google Spreadsheets


google-spreadsheet
Can anyone explain why this returns false when I try to equate the two? The two are exactly the same. I'm basically trying to do a validation here and make sure the numbers match up. I want to check if cell B4 equals cell L2 on a different spreadsheet. The...

Ignore #VALUE! error in SORT function


sorting,google-spreadsheet
I have the following columns of data and would like to combine them while deleting any duplicates. I am currently using this formula: =sort(unique(transpose(split(join(";",A:A)&join(";",B:B),";"))),1,TRUE) The desired outcome would be: A 1. a 2. c 3. d 4. f 5. h 6. i 7. n 8. r 9. s However, due...

Google Spreadsheet custom function to add csv in spreadsheet


google-apps-script,google-spreadsheet,google-spreadsheet-api
I am using Google spreadsheets, and in my google spreadsheet i want to import data from my RESTful web service. I cant use =IMPORTDATA due to the fact that i need to add a header to the url for auth purposes. This is the custom function i have so far:...

Google spreadsheet script authorisation to BigQuery


google-apps-script,google-spreadsheet,google-bigquery
I have a Google spreadsheet with a script that connects to BigQuery (using this tutorial - https://developers.google.com/apps-script/advanced/bigquery?hl=ar-AE). It adds an extra menu option and users can run the script that executes a query to BigQuery. It works fine for me and I want to share this spreadsheet with other users...

Macro or other solution in excel to automate interaction of data in two worksheets to come up with a third worksheet


excel-vba,google-spreadsheet
I have no idea where to start with this problem (may be due to poor knowledge of terminology). I have two worksheets which I will put up in Google Sheets which I'd like to generate a third worksheet for (also in the same Google Sheets file). https://docs.google.com/spreadsheets/d/1ALQlQhNugUnQzM5NdbFsLX_TlhV6BzT_1TDQKc6hD5I/edit?usp=sharing I'd like to...

SPLIT using “ ” delimiter in Google Sheets won't always preserve period following number


regex,string,split,google-spreadsheet,string-split
I'm using the SPLIT function to divide text around white spaces (" ") in strings. However, the output is inconsistent when a number is immediately followed by a period. Column A below contains strings, and column B the function =SPLIT(A1," ") copied down: Note how cell B1 does not contain...

fetching specific columns using listFeed in googlesheet using java api


java,google-spreadsheet,google-spreadsheet-api
I want to fetch only specific columns based on header in spreadsheet. I tried the following code ListQuery query=new ListQuery(url); query.setFields("FieldName"); but it gives the following error: Exception in thread "main" com.google.gdata.util.InvalidEntryException: Bad Request Fields query parameter is not supported at com.google.gdata.client.http.HttpGDataRequest.handleErrorResponse(HttpGDataRequest.java:602) at com.google.gdata.client.http.GoogleGDataRequest.handleErrorResponse(GoogleGDataRequest.java:564) at...

Google Spreadsheet (Using named range in script)


google-apps-script,google-spreadsheet
Trying to get my script to use a named range. In that range I am trying to reset the cell colors back to white. I keep getting an object error, can't figure out how to make this work. function resetCheckDirectory() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var tableRange = ss.getRangeByName("rangename"); tableRange.setBackgrounds("#FFF");...

How do I make a Sidebar display values from cells?


google-apps-script,google-spreadsheet
I have a Google Sheet that I'm using as a database of clients. There are quite a lot of variables (columns) that I need to store per entry, so I thought that it would be easier to view each entry if I could have a Sidebar display all of the...

Copy Row if Sheet1 A contains part of Sheet2 C


rss,google-spreadsheet,google-docs,drive,advanced-search
So I'm trying to pull the data in a row from a separate sheet (sheet2!), if part of Col A has the the Date that is in sheet1! C1. Col A ex: "Build 251 at Fri Jun 12 03:03:49 2015" Col C1 ex: "Fri Jun 12" (Changes date every couple...

How do i stop google sheets skipping a row with my formula when a new answer is entered?


google-spreadsheet,formulas
Every time a new row of answers is added the formula on another shit will skip the already referenced rows where the new data has just been added. If i manually drag it down across a number of fields it updates properly and shows the correct number. However if i...

Are there google apis to update a google sheet


google-api,google-spreadsheet
I want to listen to calendar events and update cells in the google sheet. I have found apis for calendar but are there any apis available to update cells in google sheet ? https://developers.google.com/google-apps/calendar/v3/reference/ Thanks...

Counting unique values in Google Spreadsheet based on multiple columns


google-spreadsheet,unique
Let's take this spreadsheet for example: ID | StoreName | StoreID | CheckinTime | User 0 | w1 | 1 | 10:00 | user1 1 | w5 | 1 | 10:01 | user2 2 | w2 | 1 | 10:01 | user1 3 | w1 | 1 | 10:01 |...