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

using AND/OR operators together


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?


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.


Function to check for first blank cell

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 unique values in Google Spreadsheet based on multiple columns

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 |...

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

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...

Google sheet app script errors after laptop sleep

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...

Nslookup or dig in Google App Script

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...

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

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...

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

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<-"" d <- gsheet2tbl(url2, sheetid = 0) Error...

Yearly quarters formula

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

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

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

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...

Convert from base 10 to base 3

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?

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

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...

Google Spreadsheet setBackgroundColors() “Deprecated”

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...

Edit a Google Spreadsheet using Google Apps Script

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...

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

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...

Google Spreadsheet (Using named range in script)

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");...

Prevent users from creating new sheets in a shared google spreadsheet

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 upload a data frame into a Google Spreadsheet

I would like to upload a data frame into one particular workingsheet within a spreadsheet on Google Spreadsheet using: googlesheets library 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 =...

Access spreadsheet via Google API invalid_grant

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 =...

Conditionally formatting multiple rows with the same conditions

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...

Google Spreadsheet custom function to add csv in spreadsheet

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:...

Increment count in column based on value in column

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 i create a formula of payment using weeknum formula

i need to calculate paymant of this people in the same week like you see in this spreadsheet 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

Are there google apis to update a google sheet

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 ? Thanks...

Change/replace part of an URL contained WITHIN a function

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...

access drive with access token

struggling to understand the oauth2 token and refresh token processes ive got this code $url = ''; $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...

Add 5 Business Days to a timestamp

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 =...

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

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). I'd like to...

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

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?...

Ignore #VALUE! error in SORT function

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 Form to Document Merge to Pdf to Mail

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...

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

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 get Google Sheet data into a HTML page using jQuery/Javascript?

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

Extract matching data from varying number of row

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,...

If Statement Not Working on Google Spreadsheets

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...

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

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...

Auto populate fields in Google Sheets

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...

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

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...

How do I make a Sidebar display values from cells?

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...

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

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...

Google Sheets API for clickable images in Google Sheets

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?

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

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...

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

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...

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

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...

fetching specific columns using listFeed in googlesheet using java 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" Bad Request Fields query parameter is not supported at at at...

Google Apps Script: event.setTime error and time format

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 spreadsheet script authorisation to BigQuery

I have a Google spreadsheet with a script that connects to BigQuery (using this tutorial - 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...

Calc/Spreadsheet: combine/assign/migrate cols

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 | ... |...

Copy Row if Sheet1 A contains part of Sheet2 C

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 to implement rangeProtect() over multiple tabs - google 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...