FAQ Database Discussion Community


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 put a new column after a “sort” in a google sheets doc?

google-spreadsheet
I have the following issue: I create a column by pulling data from columns of dfferent sheets to ctreate a single column created from the unique values. I the use that column via a transpose(sort(range)) to create column headings on a third sheet. My problem is how can I then...

Merge Multiple Google Spreadsheets into One Sheet

google-spreadsheet
I have a couple of Google Spreadsheets. This Spreadsheets have the same columns number and columns names, but diffrent data. I want to merge all of this Spreadsheets into one Sheet. The closest thing i found is this: http://ctrlq.org/code/19980-merge-google-spreadsheets , but it make new Spreadsheet with diffrent Sheet for every...

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

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

How to pass the “e” parameters to a function called with a trigger?

google-apps-script,google-spreadsheet
I am working on a simple spreadsheet, and need to do some operations when my form from Google Form is submitted, and the values are inserted in the spreadsheet. Reading the docs I've found that I need triggers, it all seems straightforward, but I am missing something: function setIncomes(e){ var...

Google Sheets Addon or Formula that accomplishes the same thing as the Prevsheet Macro's for Excel

vba,google-spreadsheet
I want to have a cumulative total running on each sheet (so the current total will be a value on the current sheet summed with the cumulative total from the previous sheet, each sheet representing a day of the month. In Excel this was definable in a Macro similar to...

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

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

Can I add a Trigger based on change of a particular cell?

google-apps-script,google-spreadsheet
I have a script that is currently set to run onEdit. Ideally, I'd like the script to run ONLY when someone makes a change to cell B26 of the sheet in question. Is this possible?...

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

Using Google App Scrips to produce Input Messages

google-apps-script,google-spreadsheet
I basically want to have a set-up for our gaming community that would really help us out. I have this idea that if you put a certain value into a cell on a Google Spreadsheet, it would give a client-side pop-up box saying something. Now in the current spreadsheet we...

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

Google Sheets Script - changing the contents of a cell based on a given condition

google-apps-script,google-spreadsheet
I'm attempting to set up a submit button that changes the contents of a cell based on the selection the user makes on another sheet. [ Something along the lines of getrange(match()).setvalue() ] I can't seem to find a way to identify the correct range with the script. Attached is...

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

How do I get google sheet conditional formatting custom formulas to refer to relative cells?

google-spreadsheet,gs-conditional-formatting
I have a google sheet where I want the range A:D to format a certain color if the contents of A2 contain the word "Order" (case insensitive). Right now I have the following: Conditional Format applies to range - A:D Format cells if... - "Custom formula is" Custom Formula -...

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

Filter function based on dynamic criteria from another cell

google-spreadsheet
In google spreadsheet, I try to create a dynamic filter which depends on the content of another cell. Actually I need something like this: if A1 = "1" { filter (A70:D73;E70:E73="A") } if A1 = "2" { filter (A70:D73;F70:F73="B") } Anyone an Idea how to create such a dynamic filter?...

Email notification in google apps script

google-apps-script,google-spreadsheet
https://support.google.com/docs/answer/91588?hl=en I have a Sheet where the the cells are filled in with data that is pulled via Apps Script, which they get from a website. Sometimes they pull in the exact same data, sometimes not. On the old Sheets version I use to be able to get emails when...

How do I select a range of cells in without giving the range a name?

google-apps-script,google-spreadsheet
I would like to select cells C29:Z50 as a variable using apps script. I'd prefer not to have to create a named range (but I can if needed). My goal is to create some custom formatting beyond colors that can use built in functionality. So, if cell B27 = "Transactions"...

Google Sheets - How to create add and subtract buttons to track inventory

button,google-spreadsheet,spreadsheet
I've been searching for similar solutions out there but am coming up short so far. Here is what I want to accomplish: I need to come up with a basic solution to sync inventory quantities at the end of each day. We take physical counts of inventory sold throughout 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!...

Sum multiple values in certain rows in a unique formula

excel,google-spreadsheet,spreadsheet
I want to do a formula that multiplies 2 values on each row with the same ID (another value) of the row where the formula is. Like a sumproduct selecting only certain rows (by ID) in the range. I shared the doc here so that you can understand what I...

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

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

Using the UrlShortener API in a custom Spreadsheet function

google-apps-script,google-spreadsheet,google-docs,add-on,url-shortener
I would like to use an API (url shortener) with a public google Add-on. For the moment my code returns: Daily Limit for Unauthenticated Use Exceeded. Continued use requires signup. Is this possible? If yes, Do I need an authentification Token? If yes, what key type should I choose? How...

Date converted to plain text in google spreadsheet

google-spreadsheet
I use importdata() to grab a legacy file and use it in a spreadsheet. This file contains date's in the yyyymmdd format (i.e. 20150520) I wish to filter out dates what contain a certain date, so when I type in a cell "20150520") it work fine, but when I calculate...

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

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

COUNTA in current row in an array formula (Google Sheets)

arrays,excel,count,google-spreadsheet,row
I have a Google sheet with fixed number of columns and dynamic rows. I like to use countA to count fields with a value (non-blank) in the current row. I found a formula here but don't understand it, neither can get it to work. ArrayFormula(MMULT( LEN(A1:E)>0 ; TRANSPOSE(SIGN(COLUMN(A1:E1))))) Sheet gives...

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

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

gdata spreadsheet library for python not working anymore?

python,google-spreadsheet,gdata
I was trying to run a query for data in one of my google docs, and it's worked for several months. Starting yesterday or the day before, I noticed that my script no longer works. Has Google updated their api for spreadsheets? Has anybody found a workaround? My error looks...

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

Yearly quarters formula

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

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

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

Parsing specific text from cell

regex,google-spreadsheet,spreadsheet
I am having in a google spreadsheet multiple cells the following text(only the Language changes): Lorem ipsum dolor. sit.com<br><br>Languages: English to Dutch<br/><br/> I would only like to display English to Dutch in a separate cell. I tried this: =REGEXEXTRACT(D2,"<br>(.*?)</br>") However, I do not come very far with this solution, because...

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

ISO-8601 String to Date in Google Sheets cell

google-spreadsheet,google-docs
I have a bunch of ISO-8601 formatted strings in a column of my sheet. How can I get google sheets to treat them as Dates so I can do math on them (difference in minutes between two cells, for example)? I tried just =Date("2015-05-27T01:15:00.000Z") but no-joy. There has to be...

On Change Trigger Not Running Script On Change

google-apps-script,google-spreadsheet
I have a script to update named ranges when new rows of data are added to the spreadsheet in question: function updateNamedRanges() { // get to the right place var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('ga weekly data pull'); //now update the named ranges if they have changed in...

Selecting all rows with distinct column values - Google query language

google-spreadsheet,gdata
Consider the following table : Test Name  Bug name  Status Name1            Bug1        Fail Name2            Bug1        Fail Name3            Bug2        Fail...

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

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

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

Code gives out “strange output”

google-apps-script,google-spreadsheet
I am haveing some trubble witht this code. My output will look somthing like this. "29 days, 29 days, 0 hours, 29 minutes". The only variable outside of the code is toon.lastModified. it can be somthing like 1431769709000 . The task for the code is to tell how mutch time...

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

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

Google Spreadsheet - is there any way to export conditional fomatting script

google-apps-script,google-spreadsheet
I created a lot of conditional formatting on one of my sheets. I would like to export it as an app script so that I can use it on any other sheets (of the same type) in the future. I know how to create conditional formatting using scripts but could...

Google Sheets Copy Down Issue

google-spreadsheet,spreadsheet,google-spreadsheet-api
I have a google form that my students fill out daily. All form responses feed into one workbook. From the workbook, I have a spreadsheet for "Clean" data. Which is just the information from the responses sheet needed to graph progress. The formula I am using in A2 is: ='Form...

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

How to find text in a string using google script?

javascript,google-apps-script,google-spreadsheet
I tried indexOf(), findText() and other few methods for finding a string pattern in a text in google app script. None of the above method works. var str="task is completed"; I'm getting this string from google spreadsheet. I just want to find whether the above string contains a string "task"...

Call Google Spreadsheet script function from external app

google-apps-script,google-spreadsheet
I have Google spreadsheet with script attached to it (with the Tools->Script Editor tool). I want to call one of script function from external Android App. Is it possible? Should I use Spreadsheet API?...

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 Sheets - Conditional Formatting based on validation errors

google-spreadsheet,conditional-formatting,data-validation
Is it possible to set the conditional formatting of a cell based on whether or not the cell has a validation error? I have a drop down box and old data in the sheet that doesn't match up to the drop down box list needs to be added to my...

How to show multiple Date/Times per location?

google-spreadsheet,spreadsheet,google-spreadsheet-api
Using Google Spreadsheets, I need to enter data structured like the example below. There will be multiple "quadrants" Each "quadrant" can contain one or many "days", Each "day" can contain one or many "times". This data will ultimately be imported in some backend db (e.g. Access DB, SQL, MySQL)....

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

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

Any way te read an excel file from a Google Sheet?

excel,google-apps-script,google-drive-sdk,google-spreadsheet,google-apps
in my organization we work with Google Apps but we have one file that we need to maintain in Excel due to its complexity. All my other spreadsheets are Google Sheets and a lot of them need to get data from that excel file. So my question is: is there...

OnEdit function Does not recognize pasted (ctrl-p) Values as being a cell edit. Is it possible to get it to do so?

google-apps-script,google-spreadsheet,google-spreadsheet-api
I've got an script in my google sheet that stamps time codes into specific cells when a name is entered in the column next to it... However the script does not work when the names are copied and pasted into the column... is there a work around for this issue...

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

Using Array Formula with Split()

google-spreadsheet
I have a sheet with some data. The data set is likely to grow and I'd like the formula that refer to it on the right to grow with it. I tried this: =ArrayFormula(split(B16:B,";")) I expected to see the formula copy down the entire length of the sheet (couple of...

Formula Cell value is not updating in the reference while Using Google Spreadsheet API with JAVA

java,excel,google-spreadsheet,google-spreadsheet-api
I have this sample code in which I am trying to integrate google spreadsheet API with java. import java.net.URL; import com.google.gdata.client.spreadsheet.SpreadsheetService; import com.google.gdata.data.spreadsheet.CellEntry; import com.google.gdata.data.spreadsheet.SpreadsheetEntry; import com.google.gdata.data.spreadsheet.WorksheetEntry; public class ExpressionExample { public static final String GOOGLE_ACCOUNT_USERNAME = "[email protected]"; public static final String GOOGLE_ACCOUNT_PASSWORD = "xxx"; private static URL cellFeedUrl; public...

How can I get keyboard events in Google Spreadsheets?

google-apps-script,google-spreadsheet,spreadsheet
How can I get keyboard events in Google Spreadsheets? I want to use this events in my script to format non-adjacent cells.

How to know or calculate empty or blank cells on google sheets?

google-spreadsheet,google-docs
I have a big list of products on google sheets about 900 rows. And on these rows is the information about every product, when customers buy one I put the id of the product in ID product column. Now I want to now how many cells are blank or empty...

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?

Google Sheets - propagate column date formatting to new rows

google-apps-script,formatting,google-spreadsheet,rows,propagation
The date format in the column is not propagating to new rows, whenever new rows are being added to the bottom of the sheet. The column formatting is not automatically applied - this in regards to date, currency format, alignment etc.

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

Google Spreadsheet- Based on text in different cells, change another cell text

excel,google-spreadsheet,spreadsheet
please help me to create a simple Google spreadsheet. 1st function If value/text in cells C3, D3 & E3 ="passed"- change F3's value to "passed" else show "failed" 2nd function If value/text in any of the cell is "passed"- change F3's value to "passed" else show "failed"...

Google Docs Mail Merge script not working as of last week

javascript,google-apps-script,google-spreadsheet,google-docs
A mail merge script that we use for 5 of our google docs stopped working last week - a similar thing happened a few weeks ago when DocsList became obsolete and I was able to fix it, but now it has stopped working again. var docTemplate = "15kvC3M8b0Me3Vi3GwErQvG60BlTC0qAHMXrlc3Ocky8"; var docName...

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

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

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

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

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

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

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

Need formula for next letter in alphabet in google spreadsheet

google-spreadsheet
I've searched for a while but I can't find the function to get the next letter in the alphabet. Does it exist? A1="A" =char(A1+1); expected result "B" ...

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

Can I use a modifier in Gsheets regexmatch()

regex,google-spreadsheet
I have a formula in my spreadsheet: =sum(filter(gaUsers,regexmatch(gaCategory,"new user funnel"),regexmatch(gaAction,B16))) Cell B16 contains the string "Create Account". But in the raw data this shows as "create account". So, I'd like to enforce "ignore case" search. Something like: regexmatch(gaAction,B16)/i // ignore case modifier. Is this possible in Gsheets?...

How to SUMIF on a dynamic range that includes blanks?

google-spreadsheet,formulas
I have a column of numbers that I want to sum, but only under certain matched conditions, thus I've used SUMIF and SUMIFS with success. Now I want to make the range dynamic. Here's the working, non-dynamic, function: =SUMIF(B2:B60, "CC/*", A2:A60) Column B contains values that begin with "CC/" and...

Google Spreadsheets: Format value if it is public holiday

google-spreadsheet
I want the Date column to format those dates that are public holidays to be red color. e.g. Date ------ 14/05/2015 14/05/2015 15/05/2015 16/05/2015 .... **25/05/2015** <-- this is red color because of us holiday I have tried using the built in function of formatting value based on condition. But...

Highlight entry if not 17 numeric digits

google-spreadsheet,gs-conditional-formatting
Basically, I'm part of a community and when someone applies for membership, they have to provide a Player ID in their application. When we accept their application, we copy information into a Google Spreadsheet about them such as Player ID's and GUID. We want a way of indicating on our...

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 connect PHP GAE(Google App Engine) to Google Docs, Spreadsheets

php,google-app-engine,google-apps-script,google-spreadsheet,google-docs
I have a PHP app deployed in GAE(Google App Engine) I need to connect to Google Docs, Google Spreadsheet URL below and do all editions/manage(edit a Doc, Edit a Sheet,...etc) as like I am able to do via GAS, just like we manage the CAL API. https://developers.google.com/apps-script/reference/document/ https://developers.google.com/apps-script/reference/spreadsheet/ What...

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

Trying to use regexmatch with string negation

regex,google-spreadsheet
I would like to sum a range where a corresponding range does not contain "2nd". Here is the opposite of what I want: =sum(filter(fUsers,regexmatch(fLabel,"2nd"))) Which runs and returns 1,150. I have looked at and tried to copy these SO posts: Regular Expressions and negating a whole character group How to...

Most Common Value within Sublists

excel,google-spreadsheet
I apologize in advance if this is unclear, I will try to explain everything as best I can! I am working with a data set in Google Sheets such that Column A is a list of student IDs and Column B is a list of student behaviors. It looks something...

Create Spreadsheet using Google Spreadsheet API in Google drive in Java

java,google-api,google-spreadsheet,gmail-api
I had created some tool which was populating the google spreadsheet. It was working fine for 1 year, since today I've error Exception in thread "main" com.google.gdata.util.AuthenticationException: Error authenticating (check service name) at com.google.gdata.client.GoogleAuthTokenFactory.getAuthException(GoogleAuthTokenFactory.java:688) at com.google.gdata.client.GoogleAuthTokenFactory.getAuthToken(GoogleAuthTokenFactory.java:560) at com.google.gdata.client.GoogleAuthTokenFactory.setUserCredentials(GoogleAuthTokenFactory.java:397)...

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

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

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

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

Google Sheets moving rows between separate spreadsheets not working but between sheets is working

javascript,google-apps-script,google-spreadsheet
I have a short function that I made using the answers to other questions here but it is not working perfectly and I don't understand why! function onEdit(e) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var s = e.source.getActiveSheet(); var r = e.source.getActiveRange(); if(s.getName() == "Input Expenses" && r.getColumn() == 14 &&...

Extract last word from strings

split,google-spreadsheet,string-split
In Google Sheets I have string values in cells like so: 1 Gheringhap Street, Geelong VIC 3220, Australia I want to extract the country value from the string. The trouble is the string values have different numbers of commas so it's hard to say at which position the last value...

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

Can I move a row that is updated from another sheet?

google-apps-script,google-spreadsheet
I have 2 different sheets, the first is where all my data is stored and changes. On updating the first sheet it moves to the second sheet. I am trying to figure out if after the update, I can move a row from the second sheet to another tab in...

What is this Google Spreadsheets IMPORTRANGE() Sheet key?

google-apps-script,google-spreadsheet
I'm new to Google sheets, and I've got this spreadsheet with some IMPORTRANGE functions in them, and they look like this: =IMPORTRANGE($O$2; "Jan!B33") That first value, $0$2, is supposedly the spreadsheet key, but I'm used to that spreadsheet key coming from the HTML, for example "1QCfFIx6evcghuH4k74_ksqx6P7sb5xzUpRaoQobV6MA", or something like that....

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