Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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
2k views

Normalizing and grouping dates in Google Spreadsheet

I have a spreadsheet pulling together various readings taken of hours heater has run, electricity units use for that period, and outside temperature. For the last 3 months, the electricity usage and ...
3
votes
0answers
2k 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 ...
3
votes
0answers
2k views

Snap chart to grid or place a chart in a cell?

Is there a way to snap charts to the grid? In my spreadsheet, my chart hangs a little below my table, but I would like the chart to expand/contract with the cells on the left. If this is an easy ...
2
votes
0answers
82 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
56 views

How to automatically create a templated Google sheet?

I'm trying to figure out how to automatically create a templated Google sheets worksheet when a new entry is made in a different sheet. So the flow would be: Person's name is added into Google sheet ...
2
votes
0answers
853 views

Google Spreadsheet not automatically updating on published page

I have a spreadsheet that I share at work that does not update on the published webpage link that I send out. If new info in input, the webpage never updates even though it says it is set to "every 5 ...
2
votes
0answers
2k 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 ...
2
votes
0answers
380 views

Google Sheets Cells don't calculate when printed or viewing as PDF

Workbook: Very large workbook with multiple tabs of data and multiple tabs of reports using sumproduct, query and filter formulas. Payroll tab is a reflection of our master database so i'm not dead ...
2
votes
0answers
2k views

Can I precisely position charts in Google Sheets?

I am using Google Sheets to create a "Monthly Report" spreadsheet for a product, and am attempting to make an attractive, compelling experience for the consumers of the report. In this spreadsheet I ...
1
vote
0answers
14 views

Force Google Sheets to round up to specific fractions

I'm trying to format a google sheet to calculate the amount of raw lumber I need to purchase for a given project. Raw lumber is sold in quarters, so for example 4/4 is a one inch board, 5/4 is an ...
1
vote
0answers
16 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
10 views

Using Slack's Google Drive integration, but files open in the wrong Google profile

I use 2 different windows to manage each of my Google profiles. When I click through a link from Slack from a colleague sharing a file, it always opens up the wrong profile and I can't view it. How ...
1
vote
0answers
33 views

Difficulty appending to cell in Google Sheets

My goal is to automate making a calendar on Google Sheets. I used =DATE(C1,A1,B1) to make a date and then each following cell, I added 1 to the previous cell. However, now I want to also automate ...
1
vote
0answers
7 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
14 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
10 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
73 views

How to set Monday as start day of the week in Google Sheets date picker

How to set Monday as start day of the week in Google Sheets date picker? I have changed the locale, changed start day of the week in Google Calendar and can't find a way to solve this.
1
vote
0answers
14 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
145 views

Google sheets, data validation - how to auto-update the options from a “list in a range”

I have a dropdown in all the cells in a column. Around 150 rows. The four options inside the dropdown come from another tab (sheet), A1 to A4. This works fine. The options are listed from that tab, ...
1
vote
0answers
22 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
176 views

Duplicate sheet but keep protected range with exact same user as only editor?

The following code duplicates a sheet based on a template, and copies over the protected range permissions from the template sheet to the new sheet. However, the permission range is now there in the ...
1
vote
0answers
37 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
12 views

How to create a Google sign-up sheet

Is it possible to create a Google sign-up form accessible via iPhone etc. without the need for a Google account? We find we can open from a computer but not a phone if you don't have an account.
1
vote
0answers
88 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
85 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
824 views

How to reverse the y-axis in a Google Sheets graph?

I have data that represents the depth of an open mine in a transversal view. Currently, it looks like this: But since it represents the depth of a mine, I'd rather have it "flipped". So the y-axis, ...
1
vote
0answers
130 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
13 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
36 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
21 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
17 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
12 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
18 views

Script to place duplicate Google sheet beside the sheet it copies (instead of the end)

Is there any way to modify my script to have the sheet it's copying insert the new sheet immediately to the right of the one it's copying? I have tons of tabs in the file, and it's starting to get ...
1
vote
0answers
48 views

Script to copy values only

I have a script which copies new form data from one sheet to another. However, I only want the values and not the formatting to paste. Here is the script: function onFormSubmit() { // moves a ...
1
vote
0answers
13 views

How to format one cell to match formatting of another (even if “other” changes later)?

How can I do the Style equivalent of setting one cell equal to the style of another cell, even if the "other" cell style changes. use Case: I want all Row #2 of each sheet to look like (same style) ...
1
vote
0answers
133 views

Change text case on Google Spreadsheets so that first letter of the sentence is capitalized

Want to automatically change text case as I type on Google Sheets so that first letter of the sentence is capitalized.
1
vote
0answers
34 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
108 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
111 views

aggregate results by week, month for graphing using sheets

Is there a way to convert daily values into weekly averages for charting? Sample source sheet --------------------------------------------------- | Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc......
1
vote
0answers
133 views

Range notation and current row

I wonder how does work the following and is it documented somewhere? I have the sheet: A B ----------------- 1 2 3 =$A:$A 4 5 =$A:$A For column B (row 3 and 5) I see ...
1
vote
0answers
26 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
209 views

Any way to separately Validate and create a Dropdowns in Google Sheets?

Sample Scenario: Sheet 1 has a 3 Dropdowns from sheet 2 Sheet 2 has two lists, {"A";"B"}, and a second dynamic list that copies the first IF the value doesn't already appear on sheet 1 The behavior ...
1
vote
0answers
653 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 ...
1
vote
0answers
43 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, ...
1
vote
0answers
12 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
76 views

Multiple IMPORTHTML requests in a custom array, or in separate cells | Same speed?

I have a spreadsheet with 62 IMPORTHTML requests. Does it make any difference in speed if I put all of these inside a custom array rather than having them in individual cells?
1
vote
0answers
42 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
89 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
150 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
59 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 ...