If Statement Not Working on Google Spreadsheets
try using this = IF (EXACT(E3,E4), THEN Whatever)...
Parsing specific text from cell
regex,google-spreadsheet,spreadsheet
Maybe try =regexextract(D2, ":\s(.*?)<") ...
Edit a Google Spreadsheet using Google Apps Script
google-apps-script,google-spreadsheet
.getRange and .setValue are the methods you will want to use in whatever you are trying to do. This little function will just take the cell notation as the first argument, and then the value you want to set as the second. function setCellValue(cellName, value) { var ss = SpreadsheetApp.getActiveSpreadsheet();...
Nslookup or dig in Google App Script
google-apps-script,google-spreadsheet,nslookup,dig
Find a web service that will let you use a GET or POST anonymously to query DNS info using a RESTful API, and you'll be able to use UrlFetchApp.fetch() to access it. For example, StatDNS has a simple API. Here's a custom function that will resolve a Domain Name to...
Google Spreadsheets: Format value if it is public holiday
Thanks @Akshin Jalilov. So assume: Column A is the dates, Column B in another holiday spreedsheet is the holidays At Column C, I check if it is public holiday or Sunday: =COUNTIF(INDIRECT("holiday!$B$2:$B$14"),"="&A2)+COUNTIF(WEEKDAY($A2),"=1") If it is holiday or sunday, then the field value is >= 1 Then I add conditional formatting...
SpreadsheetApp.getActiveSpreadsheet() is breaking script
google-apps-script,google-spreadsheet
You must define the method to get data in a .gs file and call it with google.script.run. In some Code.gs file: function getSheetData() { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var data = sheet.getDataRange().getValues(); return data; } And in the html script: function onScrapeClick(){ // Disable Button this.disabled = true; // Get...
access drive with access token
php,google-drive-sdk,google-spreadsheet,google-oauth
If you want to give the end user access to your drive, you have to give your application authority to make API calls on behalf of a user (in this case you) in your domain. For this you have to set up a service account and generate a p12 key...
Date converted to plain text in google spreadsheet
Your legacy file may be formatting it as a number, in which case you need to change your G31 to a number as well. Try =Int(Text(Today(),"yyyymmdd"))
Email notification in google apps script
google-apps-script,google-spreadsheet
This looks a lot like what you're trying to do. For checking whether the value has truly changed after the edit, this gives you all the necessary information about it. Hence, you would know whether the new value is the same one or not. Implementing the "workaround" talked about in...
Counting unique values in Google Spreadsheet based on multiple columns
To produce the StoreName and uniqueCheckins output columns: =QUERY(QUERY(B:E,"select B, E, count(C) group by B, E",1),"select Col1, count(Col2) group by Col1 label count(Col2) 'uniqueCheckins'",1) However this will omit any StoreName that doesn't appear in the raw data (in your example, w4). Would this be OK?...
client error (400) using gsheet2tbl to get Google Sheet data
There's a new package for reading from Google sheets... https://github.com/jennybc/googlesheets I find that it's fantastic for this sort of work. Give it a shot... devtools::install_github("jennybc/googlesheets") # run this and it will ask for user authentication... gs_ls() gs_read(ws = "Your worksheet") ...
Yearly quarters formula
You could try this (add your reference cell with date or a date to the place I've pointed with **) =ROUNDUP(MONTH(*reference or date*)/3;0) ...
Google Spreadsheet - is there any way to export conditional fomatting script
google-apps-script,google-spreadsheet
There is no programmatic access to conditional formatting in either Google Apps Script or the Spreadsheet API. All you can do is get the current format (in Apps Script only). Issue 162: Ability to set conditional formatting in SpreadsheetApp That request has been active since 2010. I don't see one...
Merge Multiple Google Spreadsheets into One Sheet
Use embedded arrays. The simplest form is ={Sheet1!A!:B5;Sheet2!A1:B5}...
How do i stop google sheets skipping a row with my formula when a new answer is entered?
Just answered a similar question here. The workaround is to use INDIRECT() with ROW() =IF(INDIRECT("'Form Responses 1'!B"&Row(A16)) = "dog","1",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Cat","2",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Frog","3",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Bird","4")))) Or =IF(INDIRECT("'Form Responses 1'!B"&Row(A1)+X) = "dog","1",IF(INDIRECT("'Form Responses 1'!B"&Row(A1)+X)="Cat","2",IF(INDIRECT("'Form Responses...
Extract last word from strings
split,google-spreadsheet,string-split
There are many way to do so. One is to split on spaces and discard what is not required. Another is with a formula such as conventional in Excel (count the spaces by comparing the length of the string in full and after substituting spaces with nothing) and feed that...
gdata spreadsheet library for python not working anymore?
python,google-spreadsheet,gdata
Actually here is the answer to the problem: The use of client login (using username/password instead of oauth2) is likely the cause of the error. That protocol was deprecated 3+ years ago and was just shutdown. If you capture the HTTP response (which appears to have some HTML content), that...
Automatically export incoming emails from Outlook to existing Google spreadsheet [closed]
email,outlook,google-spreadsheet,export
Yes, it is. You can develop an Outlook add-in, see Walkthrough: Creating Your First Application-Level Add-in for Outlook to get started. The NewMailEx event is fired once for every received item that is processed by Microsoft Outlook. The item can be one of several different item types, for example, MailItem,...
Convert from base 10 to base 3
You could use a formula written in Apps Script: function toTernary(decNumb) { var str = ''; do { str = String(decNumb % 3) + str; decNumb = decNumb / 3 | 0; } while (decNumb >= 1); return parseInt(str); } Results: Usage: Tools > Script Editor > Blank Paste the...
How to connect PHP GAE(Google App Engine) to Google Docs, Spreadsheets
php,google-app-engine,google-apps-script,google-spreadsheet,google-docs
In PHP, you can make an HTTP Request. In Apps Script, you can create a Stand Alone Apps Script, that does nothing but take a GET or POST request, do whatever you want it to do (E.g. write data to spreadsheet), and then return something. So, you could use an...
Google Sheets Copy Down Issue
google-spreadsheet,spreadsheet,google-spreadsheet-api
As JPV commented, the "pushing down of formulae" by form submissions may be counteracted by using an array formula (in row 2): =ArrayFormula('Form Responses 1'!N2:N) And as Akshin commented, if you were in fact bringing over a couple of columns for graphing purposes, you can do it all in one...
Using Array Formula with Split()
Split() does not work in Arrayformula. There are workarounds. One involves using regexextract, and looks like this =ArrayFormula(IFERROR(REGEXEXTRACT(";"&B16:B,"^"&REPT(";+[^;]+",COLUMN(OFFSET(A1,,,1,3))-1)&";+([^;]+)"))) The ";" is the delimiter, the "3" would be the maximum splits/column outputs you would have in one cell. E.g: a cell with value; value; value --> would have a 3 column...
Google Sheets - Conditional Formatting based on validation errors
google-spreadsheet,conditional-formatting,data-validation
It is impossible to do that the way you want, but there is a workaround. You can use conditional formatting with a custom formula. If you data validation is from a range, use that range. If it is from a list, create a range with that list. For example a...
How to know or calculate empty or blank cells on google sheets?
google-spreadsheet,google-docs
Google sheets has a formula for that. =COUNTBLANK(range) ...
Google Docs Mail Merge script not working as of last week
javascript,google-apps-script,google-spreadsheet,google-docs
This appears to be Issue 3206, "Sending mail with attachment of Drawing (as PDF) consistently fails". Visit that defect and star it for updates. There doesn't appear to be a new scenario in your script, but if you have any additional info that might help the Googler who is responsible...
How do I get google sheet conditional formatting custom formulas to refer to relative cells?
google-spreadsheet,gs-conditional-formatting
$ is to "anchor" to that row/col. If you want the column to stay static, use $A2 and the row can adjust freely.
Copy Row if Sheet1 A contains part of Sheet2 C
rss,google-spreadsheet,google-docs,drive,advanced-search
See if this works: =query('GitHub-Changelog'!A:F; "where A contains '"&C1&"' ") where C1 (on the same sheet as the formula) is the cell that holds the date (ex: Fri Jun 12)....
Output Google Spreadsheet to XML/RSS/Atom using Google AppScript Content Service
xml,google-apps-script,rss,google-spreadsheet,google-docs
getRange() function only assigns a range. You need to use getValues(). try changing title to this. var title = ss.getSheets()[0].getRange("A1:A").getValues();...
How to put a new column after a “sort” in a google sheets doc?
The best way for me to do this is create a custom function though google script and then use it as a formula. You can use the following script //Do Not Edit This function ADDTOTAL(range) { return range+",Total"; } And then in your sheet type with your range =SPLIT(ADDTOTAL(SORT('REPORT LOOKUP'!F:F)),",")...
Counting values embedded in strings inside a column (Google Spreadsheets)
excel,google-spreadsheet,excel-formula,formula,countif
You would have to use wildcards.. please try: =countif('Responses'!B:B,"*"&A2&"*") and see if that works ?...
Can I add a specific sentence to a cell based off of another cells answer?
google-apps-script,google-spreadsheet
Use the debugger, to step through your code one line at a time. In order to do that, you'll need to make some changes to your code. Hard code some values, just for testing: function onEdit(e) { var editedCol = 24; //e.range.getColumn() var editedValue = 99; //e.range.getValue() var editedRow =...
Google Spreadsheet (Using named range in script)
google-apps-script,google-spreadsheet
You are making a simple spelling error, setBackground in this case is without s because you are setting a single color to multiple cells (the argument is "#FFF") So the code is : function resetCheckDirectory() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var tableRange = ss.getRangeByName("NamedRange1"); tableRange.setBackground("#FFF"); }; to summarize : setBackground...
Trying to use regexmatch with string negation
You might want to use a built-in NOT() operator (see Filter Sample Usage): =sum(filter(fUsers,not(regexmatch(fLabel,"2nd")))) EDIT: Accoding to RE2 regex syntax, negative look-aheads are not supported. (?!re) before text not matching «re» NOT SUPPORTED This means, you cannot check with a regex if a sequence of characters is missing in the...
How to pass the “e” parameters to a function called with a trigger?
google-apps-script,google-spreadsheet
It was a bug. I have noticed that is better to delete the trigger, save the script, and re-create it when changes are made to the function that it calls. This way I never had problems, not doing caused strange behaviors.
Need formula for next letter in alphabet in google spreadsheet
Use this instead: =CHAR(CODE(A1)+1) ...
Google Sheets Addon or Formula that accomplishes the same thing as the Prevsheet Macro's for Excel
You can do that only using a script. Try this: function PrevSheet(range) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets(); var active = ss.getActiveSheet().getName(); for (i in sheets) { if (sheets[i].getName() === active) { return (sheets[i-1].getRange(range).getValues()); } } } The only problem is, you will have to push the...
What is this Google Spreadsheets IMPORTRANGE() Sheet key?
google-apps-script,google-spreadsheet
That's an absolute cell reference. (The sheet's key is expected to be read from cell O2.) This range syntax is supported by Excel and Google Sheets....
Any way te read an excel file from a Google Sheet?
excel,google-apps-script,google-drive-sdk,google-spreadsheet,google-apps
im giving you an algorithmic-level answer without code because you also have none. with apps script you can import the xls to drive as a new spreadsheet at regular intervals. then, copy its contents to a fixed spreadsheet (replacing previous content). other spreadsheets can read from that fixed sprradaheet with...
Google Spreadsheet custom function to add csv in spreadsheet
google-apps-script,google-spreadsheet,google-spreadsheet-api
I guess this way cant work, based on this article, others are facing the same issue. I went with the image workaround that assigns the script to an image and gets called when you press the image. The following error message is displayed: You do not have permission to call...
Javascript (Google Scripts) value from a function is not returning
javascript,google-apps-script,google-spreadsheet,google-calendar
Even if I'm not A JavaScript expert, I'd find it more logical to write it like that (assigning a value directly to the EventId variable) : ... var EventId = createEvent(calendarId,title,startDt,endDt,desc); Logger.log('id after func = '+EventId); sheet.getRange(lr,EventIdHolder,1,1).setValue(EventId); } function createEvent(calendarId,title,startDt,endDt,desc) { var cal = CalendarApp.getCalendarById(calendarId); var start = new Date(startDt);...
Code gives out “strange output”
google-apps-script,google-spreadsheet
This line: sinceLastMod=sinceLastMod+sinceLastMod+lastModH+" hours, " Should be: sinceLastMod=sinceLastMod+lastModH+" hours, " Remove one of the sinceLastMod variables. You've got it in there twice....
Are there google apis to update a google sheet
You can use the Google Sheets API. But you are probably better off using Apps Script to access Calendar and Sheets.
Google Apps Script: event.setTime error and time format
google-apps-script,google-spreadsheet,google-calendar
Serge has it right - the problem is that you've retrieved a CalendarEventSeries object, not a CalendarEvent. Since the only method in the service that will look for an event by ID is getEventSeriesById(iCalId), you're kinda stuck. One option is to use the Advanced Calendar Service instead: var event =...
How to implement rangeProtect() over multiple tabs - google spreadsheet
function,google-apps-script,google-spreadsheet,spreadsheet
Answering my question, The main script discribs the way to find the sheet and the range(s) to protect / unprotect, for different users using the remove/addEditors(mail) function //main script - where to protect - who can edit function Protect_UnProtect( myrange, mysheet, mydescription, startRow, colnumber, numRows, columncount) { var ss =...
how i create a formula of payment using weeknum formula
google-spreadsheet,week-number
I made a extra sheet in the spreadsheet you shared, called 'JP'. In cell A1 I entered this formula: =ArrayFormula(query({weeknum(Foglio2!A2:A)\Foglio2!B2:D}; "select Col2, Col1, sum(Col3) where Col2 <>'' group by Col2, Col1 label Col2 'Name', Col1 'Weeknumber', sum(Col3) 'Total'")) This formula outputs three columns: one column with the names, a second...
Google Sheets - propagate column date formatting to new rows
google-apps-script,formatting,google-spreadsheet,rows,propagation
Create a function that will run when the form is submitted: Managing Triggers Manually - Google Documenation Code.gs function respondToFormSubmit() { //Format entire columns var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var range = sheet.getRange("M:Y"); range.setNumberFormat('dd.mm'); }; ...
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
There seems to be a bug with a possible workaround. It appears the range may be being returned to the event object but not the value. Instead of: !e.value Try: e.range.getValue().length == 0 Or: e.range.getValue() == "" https://code.google.com/p/google-apps-script-issues/issues/detail?id=4816...
Increment count in column based on value in column
excel,google-spreadsheet,excel-formula,array-formulas
In a google spreadsheet you may want to try: =ArrayFormula(iferror(SORT(ROW(A1:A),SORT(ROW(A1:A),A1:A,1),1)-MATCH(A1:A,SORT(A1:A),0)-ROW()+2)) Example sheet...
Using Google App Scrips to produce Input Messages
google-apps-script,google-spreadsheet
Here is how you do it. Add as many "else if" functions as you want. I formatted it in the it would be easy for you to edit function onEdit(e) { var cell = e.range; var cellColumn = cell.getColumn(); var cellSheet = cell.getSheet().getName(); var cellValue = e.value; var sheet =...
Call Google Spreadsheet script function from external app
google-apps-script,google-spreadsheet
Unfortunately, you cannot directly call your script from any external application. However, you can change it to be a web application, then invoke it via an HTTP GET or POST from almost anywhere, including an Android app. Insert new rows into Google Spreadsheet via cURL/PHP - HOW? provides an example...
How do I select a range of cells in without giving the range a name?
google-apps-script,google-spreadsheet
You can use Spreadsheet.getRange(). For example, this should work: var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("..."); var currencyRange = ss.getRange("C29:Z250"); Well, actually I'm using this Sheet.getRange() method instead, but they should be the same in your case. It offers more ways to declare the desired range, such as getRange(row, column, numRows, numColumns), which...
Can I use a modifier in Gsheets regexmatch()
In GDocs, you need to use a CONCATENATE function to append a subpattern to another pattern. Just using + won't do. Instead of just B16, use CONCATENATE("(?i)",B16) And the whole formula would look like =sum(filter(gaUsers,regexmatch(gaCategory,"new user funnel"),regexmatch(gaAction,CONCATENATE("(?i)",B16)))) ...
Google Apps Script: Bi-directional sync when rows are deleted
google-apps-script,google-spreadsheet
You just have to delete the contents of the sheet before you set the new values. toWorksheet.clear() toRange.setValues(thisData.getValues()); ...
Google Spreadsheet setBackgroundColors() “Deprecated”
google-apps-script,google-spreadsheet
Use setBackgrounds(). With an s since it's a method that applies multiple background colors to multiple cells
How to show multiple Date/Times per location?
google-spreadsheet,spreadsheet,google-spreadsheet-api
I’m not quite sure what answer you are expecting but wanting to post an image (and probably length!) is why this is not a comment. Poor data layout that requires changes to help legibility or changes to facilitate further processing is, IMO, a very big issue – much more so...
Create Spreadsheet using Google Spreadsheet API in Google drive in Java
java,google-api,google-spreadsheet,gmail-api
I finally with help from here managed to create such connection. Everything is working as before. Steps You have to do is: Register at https://console.developers.google.com Create new project Under APIs & Auth -> Credential -> Create New Client ID for Service Account When the Client ID is generated You have...
How do I make a Sidebar display values from cells?
google-apps-script,google-spreadsheet
Something like this? This add-on uses the poller idea from How to poll a Google Doc from an add-on to call a server function getRecord(). That function grabs the row of data that is currently selected, and returns it to the showRecord() callback on the client (JavaScript) side, which handles...
Can I add a Trigger based on change of a particular cell?
google-apps-script,google-spreadsheet
This is code taken directly from the documentation: function onEdit(e){ // Set a comment on the edited cell to indicate when it was changed. var range = e.range; range.setNote('Last modified: ' + new Date()); } It already has e.range in it. All you need to do is add and if...
SPLIT using “ ” delimiter in Google Sheets won't always preserve period following number
regex,string,split,google-spreadsheet,string-split
This may get a little 'ugly' but see if this works: =ArrayFormula(iferror(REGEXEXTRACT(" "&A1:A,"^"&REPT("\s+[^\s]+",COLUMN(OFFSET(A1,,,1,6))-1)&"\s+([^\s]+)"))) 6 in the offset denotes the (maximum) number of columns/groups that are outputted. as this is an arrayformula, no 'dragging down' is needed. ...
Change/replace part of an URL contained WITHIN a function
javascript,replace,google-spreadsheet
Instead of including DATA1 in the URL, append it with data coming from f.ex a textbox: function loadChart() { var txtbox = document.getElementById('textbox').value; var url = 'https://docs.google.com/spreadsheet/ccc?key=...&sheet=' + txtbox; var query = new google.visualization.Query(url); query.send(handleQueryResponse); } ...
Can I move a row that is updated from another sheet?
google-apps-script,google-spreadsheet
You need to change your if statement. It should be "===" instead of "==". Here is a good explanation behind this. Here is the final script that you can use function onEdit(e) { var sourceSheet = "Raw Data"; var columnNumberToWatch = 17; // column A = 1, B = 2,...
Selecting all rows with distinct column values - Google query language
Assuming you have the example table in range A1:C, I think the expected result could be achieved with this formula: =ArrayFormula(iferror(vlookup(unique(B1:B),{B1:B,A1:C}, {2,3,4}, 0 ))) See if that helps ?...
Formula Cell value is not updating in the reference while Using Google Spreadsheet API with JAVA
java,excel,google-spreadsheet,google-spreadsheet-api
getCell() only reads the local information about the cell, i.e. the stuff you inserted. You'll have to get the cell again from a CellFeed, then go through the entries to find you cell again to read back the online information. service.getFeed(cellFeedUrl, CellFeed.class); for(CellEntry ce : cellFeed.getEntries()) { Cell cell =...
Google sheet app script errors after laptop sleep
google-apps-script,google-spreadsheet
Henrique provides a good explanation of why custom functions like this don't update in Google Apps - script to summarise data not updating. It comes down to an optimization decision in Google Sheets; a custom function will only be re-evaluated if its parameters have changed. Try to set up an...
Can I associate a user's account with an action to the drive API?
javascript,oauth,google-drive-sdk,google-spreadsheet,google-oauth
The short answer is no. You'll be using the spreadsheets API (NOT the Drive API) to update the sheet.As far as Google is concerned, the "user" is your application, regardless of which human was driving the application at the time. Your application knows who the human is, and so it...
Function to check for first blank cell
javascript,loops,google-apps-script,google-spreadsheet
I am not sure of what is the format of your startRow and startCol variables, nor to understand what you are trying to do but I think you should update the variable range in the while loop, because isBlank tests if the whole range is blank or not : https://developers.google.com/apps-script/reference/spreadsheet/range#isBlank%28%29
How to SUMIF on a dynamic range that includes blanks?
I may be missing out on something, but can't this be accomplished by simply using open-ended ranges ? =sumif(B2:B, "CC/*", A2:A) ...
COUNTA in current row in an array formula (Google Sheets)
arrays,excel,count,google-spreadsheet,row
The formula should work if you convert the booleans (true or false) returned by LEN(A1:E)>0 to numbers (1 or 0), as Barry already mentioned. This can be done quite easily by wrapping the output of the LEN()-function in an N-function or by preceding it with '--'. So, assuming your data...
Filter function based on dynamic criteria from another cell
I've found a kinky solution for this. first I've divided the problem in some smaller portions. I've created an extra column what I call the 'filter' column. Next to this filter column i've matrix with all posible filter options In this spreadsheet you can see an example of this solution...
Access spreadsheet via Google API invalid_grant
java,google-api,google-spreadsheet,gmail-api
Try adding access_type with the value offline to your auth request. I had a very similar error to this not 2 days ago and this fixed the problem. Also, just to confirm the clientID value should be similar to @developer.gserviceaccount.com They basically expect the email_address value from the console api...
Google Sheets moving rows between separate spreadsheets not working but between sheets is working
javascript,google-apps-script,google-spreadsheet
I believe I was able to solve this in a simulation environment. First of all, the onEdit method apparently doesn't work when you access other SpreadSheets as it is supposed to be for simple scripts only. So you'll need to create a function with another name and run it as...
Prevent users from creating new sheets in a shared google spreadsheet
google-spreadsheet,google-docs
Without seeing your existing spreadsheet, Seedmanc, Google's "Filter views" option under the "Data" menu of your Sheet could be helpful, because it lets owners/editors create and save filters that are accessible to view-only users too. What's more, view-only users can create filter views unique to them. Once they enter a...
How to find text in a string using google script?
javascript,google-apps-script,google-spreadsheet
You need to check if the str is present: if (str) { if (str.indexOf('task') > -1) { // Present } } Alternatively, you can use test and regex: /task/.test("task is completed"); /task/.test(str); /task/: Regex to match the 'task' test: Test the string against regex and return boolean ...
Google Sheets - How to create add and subtract buttons to track inventory
button,google-spreadsheet,spreadsheet
You can use Google Apps Script for this. If you are unfamiliar, in your particular spreadsheet, go to Tools → Script Editor and then select the Blank Project option. Then you can write functions like this to achieve what you want! function subtractSold() { var sheet = SpreadsheetApp.getActiveSheet(); var c1...
Highlight entry if not 17 numeric digits
google-spreadsheet,gs-conditional-formatting
Go to Format tab in the google spreedsheet, then go to Conditional Formatting, then in the Apply to range box, put down your range, say A1:A30 the range where you want to apply your formatting. Then, in the Format cells if, choose Custom formula is, the last one in the...
fetching specific columns using listFeed in googlesheet using java api
java,google-spreadsheet,google-spreadsheet-api
You can select columns if you use the Visualization Query Language Example that selects columns B and C (2nd and 3rd) of a big sheet (from this tutorial): https://docs.google.com/a/temet.ch/spreadsheets/d/1jwKyiVro8ZqPr5jRsyCN7igPJ76vOvtRy_xq3I8nKVI/gviz/tq?tqx=out:html&gid=0&tq=select%20B,C The query returns JSONP if you leave away the tqx=out:html parameter, but you won't get ListEntries. Note that the column names...
Google Sheets Script - changing the contents of a cell based on a given condition
google-apps-script,google-spreadsheet
I took a look at your spreadsheet and added some code: function forButton() { //Logger.log('it ran!'); var ss = SpreadsheetApp.getActiveSpreadsheet(); var activeSheet = ss.getActiveSheet(); var masterSheet = ss.getSheetByName('Master'); var inputValues = activeSheet.getRange(2, 1, 1, 3).getValues(); var secondInput = inputValues[0][1]; var allColumnTwoVals = masterSheet.getRange(2, 2, 6, 1).getValues(); allColumnTwoVals = allColumnTwoVals.join().split(","); Logger.log('secondInput:...
How can I get keyboard events in Google Spreadsheets?
google-apps-script,google-spreadsheet,spreadsheet
keyboard events for cells cannot be handled from apps script. you can only detect a cell change once it has beeb commited (user typed and pressed enter for example), which is not a keyboard event (its a range edit event)
Ignore #VALUE! error in SORT function
Your existing google-sheets formula can make use of the iferror function. =sort(unique(transpose(split(join(";",iferror(A:A, ""))&join(";",iferror(B:B, "")),";"))),1,TRUE) ...
How to upload a data frame into a Google Spreadsheet
First register the Speadsheet: Test<- register_ss("Year") Second access Workingsheet: test<- edit_cells(Year, ws = "January", input = Test, header = TRUE, trim = FALSE) ...
Sum multiple values in certain rows in a unique formula
excel,google-spreadsheet,spreadsheet
Adding a criteria for column E should be sufficient to cancel out the non-matching rows. In B6 use, =SUMPRODUCT(($E$6:$E$13=E6)*($F$6:$F$13)*($G$6:$G$13)) Fill down as necessary....
Auto populate fields in Google Sheets
Assuming 6/1/2015 in F1 and 6/15/2015 in G1 please try: =array_constrain(arrayformula(row(A1:A100)+F$1-1),G$1-F$1+1,1) ...
Google Form to Document Merge to Pdf to Mail
google-apps-script,google-spreadsheet
when replacing multiple keys in a document you are quite right that the keys must be unique. Personally I bracket all keys with # symbols which works for me e.g. #key1# won't be confused with #key10#
Google Spreadsheet- Based on text in different cells, change another cell text
excel,google-spreadsheet,spreadsheet
For the first function, you would need to enter the following in cell F3: =IF(AND(C3="passed",D3="passed",E3="passed"),"passed","failed") This checks whether C3, D3 and E3 contain "passed" and if so returns "passed". For the second function, this is very similar: =IF(OR(C3="passed",D3="passed",E3="passed"),"passed","failed") This checks whether any of the three cells contain "passed", and if...
Google Sheets API for clickable images in Google Sheets
google-apps-script,google-spreadsheet,google-spreadsheet-api,google-spreadsheet-addons
Can we also insert this clickable image and link it to app script from our server using Google Spreadsheet API? No. You can't even change the color of a cell. The best you can do is change a formula in a cell. Or Can we call app script from...
Google spreadsheet script authorisation to BigQuery
google-apps-script,google-spreadsheet,google-bigquery
Unfortunately you can only allow access to scripts running as 'you' if it is running as a web app. The only way to run it as a webapp is if the doGet()/doPost() function is called by the browser. Running the doGet() as a function runs it as a normal script....
How do I stop google sheets reassigning formulae values on another sheet
Here is a workaround. Use INDIRECT() with ROW() =INDIRECT("'formsitesheet'!A"&ROW(A1)) Or =INDIRECT("'formsitesheet'!A"&ROW(A1)+X) Where X is your offset. Hope this helps...
ISO-8601 String to Date in Google Sheets cell
google-spreadsheet,google-docs
Try this =CONCATENATE(TEXT(INDEX(SPLIT(SUBSTITUTE(A1,"Z",""),"T"),1),"yyyy-mm-dd")," ",TEXT(INDEX(SPLIT(SUBSTITUTE(A1,"Z",""),"T"),2),"hh:mm:ss")) Where A1 can be a cell with ISO-8601 formatted string or the string itself....
Extract matching data from varying number of row
google-spreadsheet,spreadsheet,pivot-table
I think this is possible using a formula: ={QUERY(A2:D,"select A,count(D) where A <> '' group by A pivot C"),QUERY(A2:D,"select count(D) where A <> ''group by A pivot B")} Here is an example sheet (the formula is in cell F1): https://goo.gl/T9xI1v...
How can I exclude a sheet in an array, and sort with a range
google-apps-script,google-spreadsheet
There are two questions in here: You need the index of the master sheet. Say it's the first sheet: var allSheets = ss.getSheets(); //get all the sheet var indexOfMaster = 0; //or whichever the index is var sheetsExceptMaster = allSheets.splice(indexOfMaster,1); //splice out 1 item at this index, which would be...
Add 5 Business Days to a timestamp
As far as I understand adding five business days to current date is same as adding a week. Try this: var time = new Date(+new Date + (1000 * 60 * 60 * 24 * 7)); time = Utilities.formatDate(time, "GMT-08:00", "MM/dd, E"); ...
Most Common Value within Sublists
Are you familiar using PivotTables? You could just create a PivotTable with ID as a Row Label and Behavior as a column label and Value. Then it would just be a matter of copying/pasting those values and using a MAX formula to get the greatest behavior count.
Using the UrlShortener API in a custom Spreadsheet function
google-apps-script,google-spreadsheet,google-docs,add-on,url-shortener
EDIT: The OP pointed out in the comments that this is a custom function. Custom function run with limited authorization. A complete list of what is available is at: https://developers.google.com/apps-script/guides/sheets/functions#using_apps_script_services Below uses the REST API to get the shortened url. This will work with custom functions. You will just need...
Macro or other solution in excel to automate interaction of data in two worksheets to come up with a third worksheet
I found a solution by Spioter at Excel vba to create every possible combination of a Range which proved immensely helpful! Modified the script to fit my needs and sheet which does not reflect the Google Sheet link in the question. Thank you Spioter! Sub sub_CrossJoin() Worksheets("Combinations").Range("A4:B2000").ClearContents Dim rg_Legend As...
On Change Trigger Not Running Script On Change
google-apps-script,google-spreadsheet
Run it on a time trigger that runs every minute until Google addresses the issues of not catching the on change event and/or not being able to define open-ended named ranges. Edited for running the script on open To keep the sheet from recalculating everytime it is opened whether needed...
Google Sheets Adds-on open automatically when user opens a sheet or clicks a link in worksheet
google-apps-script,google-spreadsheet
Sure, you can get a UI component for an add-on to open automatically when a document is opened. This is HORRIBLE behavior for an add-on, and it should never be published because it would interfere with other add-ons. But still, it can be done. There are some restrictions, though. The...
How to get the last entry containing a certain value in Google Spreadsheet
google-apps-script,google-spreadsheet,google-form,formulas
Assuming your data starts in row 2, try: =ArrayFormula(iferror(vlookup(unique(B2:B), sort({B2:B, A2:A}, 2, 0), {1, 2}, 0 ))) Change ranges to suit. Don't forget to include the sheetname if you need that formula to appear on another sheet....
How to get Google Sheet data into a HTML page using jQuery/Javascript?
javascript,jquery,html,google-spreadsheet,google-spreadsheet-api
There is a jquery plugin called sheetrock that does this very well, I've used it for a pretty big project myself and while it can behave strangely if you have a lot of columns in a sheet it still overall performs well: https://plugins.jquery.com/sheetrock/