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.

3
votes
0answers
3k 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

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 ...
2
votes
0answers
25 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 ...
2
votes
0answers
16 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
131 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
45 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
295 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
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
415 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 ...
1
vote
0answers
13 views

Enable grouping and ungrouping of rows in a locked worksheet (Google Sheets)

I need to share a spreadsheet with some locked cells, but it is necessary that the user can use the grouping and ungrouping functions of rows. Within the pooled range there are locked and unlocked ...
1
vote
0answers
12 views

Copy formula every other row and every column

I have been playing with scripts to try and figure out this tedious task as there are 600ish rows in my sheet, times 4 sheets. So far I have come up with this function myFunction() { var ss =...
1
vote
0answers
85 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
9 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
36 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
38 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
8 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
18 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
121 views

How do I reference the previous column of a calculated field on a pivot table? Looking to calculate percent change

I have a pivot table with a bunch of columns indicating the value over time, and would like to create a calculated field showing the change over each value. How would I reference the previous (and ...
1
vote
0answers
16 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
342 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
20 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
383 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
69 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
26 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
326 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
76 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
137 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
134 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
1k 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
229 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
18 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
46 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
22 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
27 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
13 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
20 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
81 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
1k 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
49 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
149 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
27 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
810 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
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
44 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
56 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
108 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
175 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
62 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 ...