Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. Don't use it for the Google Drive Excel files editor.

4
votes
0answers
4k views

Set title of currently selected chart from named cell

There's a sample over here that will show how to set the first's chart title to the active cell: function onOpen(){ SpreadsheetApp.getUi().createMenu('Charts').addItem('Update title of the first ...
2
votes
0answers
29 views

Transactional execution in Google Sheets Script?

I am working on a script that moves quite a bit of data around in a spreadsheet, and in some cases appends data to data. When the script finishes, clicking undo cycles through each step that the ...
2
votes
0answers
184 views

Stop circular dependency calculations unless a specific cell is edited

I am trying to figure out how to get Google sheets to only calculate circular dependency when a specific cell is changed. What is happening now is that the calculation occurs every time anything is ...
2
votes
0answers
1k views

Switch Google Sheets tabs in Firefox (Windows)

This question has been answered, apparently satisfactorily, for presumably non-Firefox browsers (see Hot keys to switch between tabs?). The accepted solution there is ctrl+shift+pg up/down. On ...
2
votes
0answers
52 views

Using INDIRECT with sheet names taken from a range

This formula I created worked in Excel: =ROUNDUP(SUMPRODUCT(SUMIF(INDIRECT("'"&$N$5:$N$41&"'!A6:A100"),A5,INDIRECT("'"&$N$5:$N$41&"'!C6:C1000"))),0) This formula is in my main sheet, ...
2
votes
0answers
2k views

Auomatically lock/protect a range or row based on date

Any one can help how to do a scripts for auto lock / protect range/row by date I have try but this script only lock a cell only ( cell M:M ) a = SpreadsheetApp.getActiveSpreadsheet(); var s = a....
2
votes
0answers
318 views

Filtering/consolidating data from multiple areas in Google Sheets

I am collecting data on a Google Form where users select a name then answer specific questions depending on what name they select. This leaves the resulting response spreadsheet with many blank ...
2
votes
0answers
3k views

Google sheets query returns “loading…”

I've put together a web application that relies on a series of Google components. Google Forms collects data from users Google sheets saves the data (automatically) Additional "calculated" columns ...
1
vote
0answers
12 views

Change button image on click

Is there a way I can change a button image on click? At the moment I am using circles over an image which I am switching between red and green. is there a way I can automate these into buttons that ...
1
vote
0answers
9 views

Can I collaborate with Google Sheets offline on multiple devices?

Can I do something about this: I manage multiple teams on sites with little or no signal except the small office WiFi spots. I want to have a copy of a single Google spreadsheet on multiple iPads for ...
1
vote
0answers
20 views

Is there a way to create a calculated field using results of another calculated field?

Is it possible to use the results of a calculated field as part of a new calculated field? I know further calculation could be done outside the pivot table but I was hoping there was a way to somehow ...
1
vote
0answers
18 views

Google Sheets Clock Cycle Counter - Increment value only on TRUE value in another cell

I want to increase the numeric value of a cell output, based on the contents of another cell input that will contain a binary value. I'd like to do this without using scripts. Cell B5 is going to ...
1
vote
0answers
26 views

SVG image doesn't display at printing on Google Sheets

I'm using SVG image on Google Sheets like this. =IMAGE("https://dev.w3.org/SVG/tools/svgweb/samples/svg-files/AJ_Digital_Camera.svg") The SVG image display on Google Sheets, but it doesn't display ...
1
vote
0answers
12 views

How to create comma and hyphen separated list of sequential numbers in Sheets?

How do I create a list of numbers, where consequential numbers are reduced to being separated by a hyphen? e.g. I have a column with numbers like 1 2 3 5 6 7 8 10 12 I want this to be reduced to 1-3,...
1
vote
0answers
44 views

Google Spreadsheets - Query Select A, B, MIN(C), group by A only

thanks for the help! Sorry if I'm expressing myself in a confusing manner, I'm a beginner at this... I'm trying to create a spreadsheet to help me plan and analyze grocery expenses more efficiently. ...
1
vote
0answers
207 views

Google Sheets text wrap no longer expands cell

All of a sudden I seem to be having trouble in my Google Sheets auto extending height of cell with text wrap, for certain cells. Mac OS: 10.13.6 (17G65) Chrome: Version 73.0.3683.103 (Official Build)...
1
vote
0answers
20 views

How can I disable the authorization needed to open Google Sheets Macros?

Whenever I try to open any macro enabled sheets it requires authorization, even though it's a first party application. Is there any way to stop Sheets from asking it or give permissions to everyone I ...
1
vote
0answers
36 views

Script to transfer data from invoice sheet to database

This is my first post and I'm new to scripts. Upon searching through multiple questions similar to mine, I couldn't find exactly what I was looking for. I want to create a macro that pulls specific ...
1
vote
0answers
350 views

IMPORTXML to import attribute = “Error: Imported content is empty”

Google sheets is giving me some trouble in retrieving span style attributes. I am trying to scrape a site (actually this) to grab some icon images for my spreadsheet. Below in red is one such example ...
1
vote
0answers
18 views

When importing a Google form submission to a spreadsheet, how do I automatically change the time format from “d-m-yyy” to “dd-mmm-yyy”

I am importing information from a Google form into a spreadsheet. It imports live. It creates a time stamp. There are also several other dates in the form (when you type it into the form there is only ...
1
vote
0answers
164 views

In Google Sheets, how can I use SUBTOTAL with an IF argument?

My N column data is either "Win" or "Loss", now I'm trying to count them separately with SUBTOTAL because I utilize filters quite often. I'm trying something like this: =SUBTOTAL(103, IF($N18:$N, "...
1
vote
0answers
11 views

Dynamic Sized Image in Google Sheets For Printing Based On Cell

I've made a calculator with Google Sheets to make it easier for Olympic shooters to train at home. The data that it produces is very helpful, but I would like to make it easier for the users to print ...
1
vote
0answers
23 views

How to Get Data from Multiple Cells and Combine into a Comment in another cell

I have a spreadsheet of orders (rows) that has room for 70 products (columns). I would like for a script to find every cell that has a value (0's are blank) and then write that value (this is the qty ...
1
vote
0answers
296 views

How can I make x-axis sorted without sorting the underlying data?

I have a data set like this: I want to plot the frequency of the 'num_of_item' as y-axis and the 'num_of_item' as x-axis. As you can see on the x-axis, the num_of_item values are not sorted. I ...
1
vote
0answers
37 views

Horizontal Scrolling Loop Macro for Google Sheets

I am looking to see if it’s possible to have a macro that would scroll a Google sheet to the end at a certain speed, and then once at the end, jump to the beginning and begin again. We are trying to ...
1
vote
0answers
29 views

getUi.prompt - How to enable confirmation with “Enter” key

When using SpreadsheetApp.getUi().prompt();, how do I allow the user to simply press Enter to confirm their entry instead of having to click on OK?
1
vote
0answers
127 views

How to see Sparkline for Mutual Funds using googlefinance?

In Google sheets when I try to plot the mutual fund price for the last one year using sparkline function, I keep getting an error. When evaluating GOOGLEFINANCE, the query for the symbol: ...
1
vote
0answers
33 views

How to create a user defined function that maps one value to another in Google Sheets

I would like to create a mapping function that takes as an input 3 arrays of the same length and a double between 0 and 1. Array #1: Each element is the minimum value of the nth bin. Array #2: Each ...
1
vote
0answers
167 views

How to align one axis in a two axis chart

I want to align my right vertical scale with my left vertical scale so both have the 0 at the same height. I have this: And I want this (I've done it manually but it's not perfect, look how both 0s ...
1
vote
0answers
252 views

Google Apps Script - how to hide run message

I have a question about running apps script on a Google Sheet. I have a few scripts written that can apply a filter and remove a filter. When I run the script from the button on the spreadsheet the ...
1
vote
0answers
247 views

Sync Google Spreadsheet to Google Calendar (remove any duplicates)

I'm trying to sync my Google sheet to my Google Calendar. However, I keep running into errors. I've tried a myriad of scripts, but none seem to work. I got one to work, but every time I ran the script,...
1
vote
0answers
372 views

My ImportXML with XPath doesn't work

I've got a problem. I'm trying to import data in my google sheet document using =ImportXML with XPath from this page : https://www.boursorama.com/bourse/produits-de-bourse/cours/warrants/...
1
vote
0answers
24 views

From a time cell and a percent cell for historical graph in Google Sheets

I have a stock spreadsheet showing both the performance of individual stock and also of the entire portfolio. I have the stock price automatically pulled from Yahoo! finance, continuously updated ...
1
vote
0answers
68 views

Google Sheets White Background is not White

When I try to print a Google sheets document, it takes way too long to print. Printing from excel or anything else does not have this problem. Hopefully I can explain correctly. I'm seeing that the ...
1
vote
0answers
25 views

How to recover spreadsheet?

I have tried restoring the spreadsheet from the version history. But still, I am unable to restore the data. I update the sheet every day, it has been updated until a day before yesterday completely. ...
1
vote
0answers
48 views

Chart labels disappear (stays outside the graph area) Google sheets

Really count on your help here, as it seems like I tried everything already. Take a look at the graph: the last value label disappears (the location is right on the inner border of the graph, so the ...
1
vote
0answers
17 views

Want to integrate template responses (from Sheets) into other apps

I teach online classes and have to create feedback for every class. In a Google Sheet, I have template responses for each class (labeled by level, unit, learning cycle, class (L1-U1-LC2-7 for example))...
1
vote
0answers
2k views

Showing percentages in google sheet bar chart

It looks like bar charts in Google Sheets won't support percentages. Is this correct? If so, how would a change the populating query that currently returns "count of"? A single cell used as the ...
1
vote
0answers
839 views

How do you paste without format by default in Google Sheets?

In Google Sheets you can hit Ctrl+Shift+V to paste without the formatting, but, is there a way to change it so that you can use Ctrl+V? Either by changing the Keystroke or forcing the rows or columns ...
1
vote
0answers
77 views

Moving linear regression on google spreadsheet

I have a table with four columns: A: date B: values C: projection D: short-term projection The first two are hardcoded values. We admit that dates go up, while values mostly go down. I used a ...
1
vote
0answers
226 views

how export a PC build to a spreadsheet

PC part picker has an export to HTML button, which is all fine and dandy. I would be more interested in exporting to an actual spreadsheet, however. Google sheets would be fine, or CVS, anything ...
1
vote
0answers
32 views

Duplicating rows in a Google Sheet

Suppose I have a Google Sheet of companies: Company Name, Link Apple, Apple.com Google, Google.com PayPal, PayPal.com and roles: Role CEO CTO CFO How would I create a Google Sheet that "matrix ...
1
vote
0answers
13 views

How can I receive a notification for new reviews for an add-on in the Google Sheets add-on store?

I would like to receive a notification (preferably email) for every new review left on the add-on store page for a specific add-on. Example for such a page: Sheetgo Review section:
1
vote
0answers
2k views

How to copy from Google Sheets into a Drawing in another Sheet on macOS Chrome

I'd like to copy an object from one Google Sheet to a Drawing embedded in another Google Sheet. From what I've read, this must be done via the Copy / Paste commands within the Google Sheets web app. ...
1
vote
0answers
46 views

Inserting Time Date into Google Sheets produces Numero sign №

I am trying to use the shortcut ⌘+Option+Shift+; to insert current date and time with Google Sheets on Chrome on MacOS Sierra. However, this produces the Numero sign №. I have searched the system (...
1
vote
0answers
60 views

Creating an array that shows Frequency + Location of individual values

I have an array filled with letters that occur in a particular order. The length of each row varies AND where the row starts also varies. I need a formula that searches each row, counts each letter ...
1
vote
0answers
150 views

Notify of user edits and not of IMPORTRANGE edits

(I think protect sheets/range will help here but besides that.) Let's say I have a sheet called Alcohol that imports from other sheets Tissue, Pencil and Physics and is editable by other users. How ...
1
vote
0answers
226 views

Link Google Sheet cells to Google Map polygon attributes

I have a Google Spreadsheet with values for things like property addresses and contact details. I have a custom Google Map with polygons that match up with the spreadsheet data. Would it be at all ...
1
vote
0answers
72 views

Function always parses column in first row but would like it to repeat

I am trying to make a function that repeats in each row of a column that fetches the URL in the first column, does an xpath query and gets an image. Currently its all working other than the fact it ...
1
vote
0answers
142 views

Charts show '1' for zero values

I have a big file with charts. For calculations I formatted all values with the following string: #;-#; to avoid the showing of zeros in charts. Now data labels which should be empty are shown as 1. ...