Check cell for a specific letter or set of letters
You can use RegExMatch: =IF(RegExMatch(A1;”Bla”);”YES”;”NO”)
google-sheets
Displaying Lakhs and Crores in Google Sheets
This works in Google Sheets for sure. Should work in Excel too. With the appropriate cells selected, navigate to: Google Sheets : Format -> Number -> More Formats -> Custom Number Format Excel : Format -> Cells -> Custom -> [Custom text box] Enter one of the following and Apply: A] For Lakhs and Crores … Read more
Google SpreadSheet Query: Can I remove column header?
Try this: =QUERY(H4:L35,”select sum(L) where H=’First Week’ label sum(L) ””)
Count rows with not empty value
I just used =COUNTIF(Range, “<>”) and it counted non-empty cells for me.
How can I do time/hours arithmetic in Google Spreadsheet?
When the number being returned by your formula is being formatted as a time, and you want it formatted as a plain number, change the format of the cell to a plain number format: click the cell and then click Format, Number, Normal. Time values in Google spreadsheet are represented as days and parts of … Read more
How to get the correct range to set the value to a cell?
The following code does what is required function doTest() { SpreadsheetApp.getActiveSheet().getRange(‘F2’).setValue(‘Hello’); }
Apply formula to the entire column
It looks like some of the other answers have become outdated, but for me this worked: Click on the cell with the text/formula to copy Shift+Click on the last cell to copy to Ctrl + Enter (Cmd + Enter on Mac.) (Note that this replaces text if the destination cells aren’t empty)
Get first item from Split()
You can use the index function to select which value to return. So to retrieve the second value from your example you could use: =index(SPLIT(“1.23/1.15”, “https://stackoverflow.com/”), 0, 2) The last argument says which column you wish to retrieve – 1 would retrieve the first value. Alternatively you could use left / right and find to … Read more
How to debug Google Apps Script (aka where does Logger.log log to?)
UPDATE: As written in this answer, Stackdriver Logging is the preferred method of logging now. Use console.log() to log to Stackdriver. Logger.log will either send you an email (eventually) of errors that have happened in your scripts, or, if you are running things from the Script Editor, you can view the log from the last … Read more
Google Sheets API returns “The caller does not have permission” when using server key
To solve this issue, try to: Create a service account: https://console.developers.google.com/iam-admin/serviceaccounts/ In options, create a key: this key is your usual client_secret.json – use it the same way Make the role owner for the service account (Member name = service account ID = service account email ex: thomasapp@appname-201813.iam.gserviceaccount.com Copy the email address of your service … Read more