function onOpen() is not running

My function includes adding a menu and toast to the document. I have verified that the trigger (onOpen) is set as well. It only works when a user goes into Tools, Script Manager, Run. We have too...

Google Sheets: How to replace text in column header?

I have a query like this =QUERY(B2:C9; "select (C * 100 / B) - 100") in my Google Sheets. What is displayed as a column header is: difference(quotient(product(100.0()))100.0()). I want to put a...

External API call in Google Spreedsheet is possible?

I have created a Google Spreadsheet with five columns; Once the user filled the values in the first three columns, It has to call a 3rd party API and fill the value(response) in the forth and...

Google Spreadsheet ImportXML Error: Imported XML content cannot be parsed

This function seems to work in general, but in this case: URL: http://www.londonstockexchange.com/exchange/prices/stocks/summary/fundamentals.html?fourWayKey=GB00B1YW4409GBGBXSET1 XPATH:...

Import or Open gsheet file in Google Docs or Google Sheets

I have a local file named timeline.gsheet , which is apparently generated by Google Docs. How do I import, edit, and save this file in Google Docs? From Google Sheets: File -> Import ->...

Font weight BOLD using formula in cell

I'm using Google Spreadsheet to make some calculations. At the bottom of my table I need SUM, AVG and some others and everything is fine. But I made a long cell with all the text, like this: ="Hi...

Reset Column and Row sizes to default

I am building a script (accessed through a custom menu item) that will also include some styling. Things like setting background colours, changing the width of columns, the heights of rows and...

BottomSheetDialog with transparent background

I would like to display a bottom sheet dialog less wide than the screen width. For instance, the Share option from Google Play Music on a Nexus 9. Do you know how to achieve this ? For now I just...

How to GetComments from google sheets

I want to get comments from google sheets based on cell range in my script program But found that range.getComment() method is deprecated and now returning notes only. Please let me know what is...

Google Script to replace IMPORTXML(too big xml to import with IMPORTXML)

I want to import a Google Shopping XML to a Google Spreadsheet, but Google Spreadsheet shows a error: Error Resource at url contents exceeded maximum size. I need to import the data (and update it...

How to read value of fetched cell data as date google sheets API

I have a spreadsheet that have dates formatted as "Jan 30" etc, but have actual date values. I fetch the data using python google Sheets API like so: def get_spreadsheet_sheets(service,...

Formulaa Convert Date format DD/MM/YYYY to MM/DD/YYYY in Google Sheets

I'm having an issue with Datevalue function cause it's not reading DD/MM/YYYY date format. It's giving Error DATEVALUE parameter cannot be parsed to date/time. I tried changing the number format...

Google Sheet formula for cumulative sum with condition

I have a Google Sheet with the following layout: Number | Counted? | Cumulative Total 4 | Y | 4 2 | | 6 9 | Y | 15 ...

Include missing dates as "0" in SELECT ... A,count(A) group BY (Google Sheets, or SQL)

I am creating a histogram of dates using SQL and I want to have "0" values for the missing dates. The specific SQL system I'm using is Google Sheets. Here is my example: What I really want is for...

Data Studio - TODATE(input, "%Y%m") Is One Month Behind

I would like to plot a time series by month, but my input data is formatted as "%Y-%m-%d". So, I create another time dimension with the following formula: TODATE(date, "%Y%M") My issue is that...

Get exchange rate on a specific date from GoogleFinance

I'm having trouble stopping the googlefinance function on a specific date to use "that" specific exchange rate to convert currency from GBP to EUR. Here is my formulae:...

Converting "##h ##m" to ##.## hours

My time tracking app exports employee hours in the following format: xxh yym I'm looking for a way to convert this into a decimal number of hours. For example, input(06h 30m) return(6.5) Can't...

Apply a "row banding" theme to a range

I am a beginner to Google Apps Script but use it to automate some simple repeating tasks. I have several spreadsheets I am copying content on a weekly basis and export them as an .xls file that I...

Merging multiple google sheets into one sheet faster

I have a google sheet where I have around 50-60 more tabs. All the data in those sheets is in the same format, I just need to merge together into a master spreadsheet. I tried the second answer...

Search entire drive contents for unresolved comments and list them for review

I would like to search the entire contents of my google drive for unresolved comments. Primarily these will be in google docs but could also be in a google sheets. If an unresolved or undeleted...

How do I export to pdf then email for only one tab of my Google sheets file?

I'm trying to export to pdf and email one of the tabs in a Google Sheets report using apps script. I found some very useful code in another Stackoverflow post. However, the pdf export is including...

Google Form random questions from a bank, different every time Form is opened

I have a list of 14 questions in a Google Sheet that I would like to use 5 of at random to populate a Google Form. I have managed to do this using the FormCreator add-on to some degree but I would...

PDI "Google Spreadsheet Input/Output" plugin compatible with Google Sheets API v4?

I am using the "Google Spreadsheet Input/Output" plugin from the Pentaho Data Integration marketplace, which is no longer maintained. Github repository link. Recently I have received an email from...

IFS function stops working when coverting Gsheet to Excel

I have a Google Sheet with a formula that is checking one column and adding a value to another based on that. It works properly in Google sheets, but the IFS function is not working when i try to...

What's the difference between getActiveCell and getCurrentCell in Google Apps Script?

I've read the definitions a few times over and I still don't understand the difference. getActiveCell:...

If statement returning wrong files for Google sheet function

Using a function that that will assign TRUE if a value is over 0 or FALSE if not. The point of this function is to work with the true values for a script. =IF(H2 > 0, TRUE) It doesn't make any...

Does microsoft excel 2016 has function "=googleFinance()" which is available in google sheets?

I want to get the live market price(Indian Market) of some stocks which are already mentioned in my MSExcel sheet, which can automatically update. I saw a function in google sheet...

Modifying Google Chart - Vertical Axis 'number format' reverts to "from source data" after I update my chart title by script (shows date) dynamically

I'm trying to update the title of my chart through the code so it is changed dynamically. When this happens, the title changes successfully, but the number format for the vertical axis changes to...

Can not run trivial app script bound to new blank document - get "This app is blocked"

I am unable to run any app script in my google account - even one created by me bound to a new document created by me. When I do, I get an "This App is Blocked" error. Note that this is different...

Cannot read a public spreadsheet when signed in, but can when signed out

I am using the Google Sheets API for client-side javascript. I cannot read a public spreadsheet when the user is signed/logged in, but can read it when the user is signed/logged out. I adapted the...