A productivity application from Google that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

3
votes
0answers
9 views

Populate column with editable range based on drop-down

So, here's my issue. I've got three sheets. One sheet has all of the classes for Mondays and Wednesdays, one sheet has all the classes for Tuesdays and Thursdays. The third sheet has a drop-down menu ...
3
votes
0answers
23 views

How can we retain authorship when we copy/paste a comment?

We have comments in a Google Spreadsheet that were not originally written by us. The cells the comments are written in already have "Conditional format rules" applied to them (e.g., cell H2). We ...
3
votes
0answers
56 views

Google Spreadsheets CSV Encoding not UTF-8?

I was assuming that Google spreadsheet exports UTF-8 encoded CSVs. However, this does not seem to be the case — my german Umlauts, etc. (äüöß, etc.) are not recognized, i.e. when opening the file in ...
3
votes
0answers
97 views

IFTTT to Google Drive (Sheets) - How to write on the same row?

I'm looking for a way to get a recipe from IFTTT to Google drive (sheets) in the same row. The recipe writes in the first 3 columns, but if there is anything already in the same row it skips that row ...
2
votes
0answers
18 views

What is the behavior when Zapier matches more than one row?

Zapier has a Lookup Spreadsheet Row - action. The docs say "Lookup Spreadsheet Row - Finds a row by a column and value. Returns all the values on that matching row." What is the behavior if the ...
2
votes
0answers
28 views

Updating/merging rows from imported data that share the same primary key in Google Sheets

I'm using an IMPORTDATA function tied to a auto-running script to pull in a .TSV data file from an obscure webform system. That all works well, and I'm able to generate a nice dashboard from this ...
2
votes
0answers
22 views

Map Chart using decimal degrees in Google Sheets

I have a column with decimal degrees (locations) and another one with values. Can I plot a Map Chart on Google Sheets (with markers)? If I have to use the Script Editor, any existing (working) ...
2
votes
0answers
16 views

Coordinates of an external named reference

In a spreadsheet, I've got a column named range: Sheet1!I:I, named NamedRanged1. In another sheet, I'd like to get the index of that named range, i.e., 9 (I is 9th column). I tried with ADDRESS and ...
2
votes
0answers
44 views

Google Sheets <copy Data from one spreadsheet to another>

Could you help me to understand how to made next couple: Table M Table 1 Name -> Name Qty <- Qty Table M has original field Name, Table 1 takes this name and ...
2
votes
0answers
29 views

In Google Sheets is it possible to print to a custom paper size?

I have a spreadsheet that I'd like to print on a single 8.5" x 22" sheet of paper. This is not a standard paper size, but my printer can print on it. The Google Sheets print settings dialog only ...
2
votes
0answers
69 views

Google Sheets - Dynamic Filter View by URI

I am using Google Forms to acquire "per-X" feedback, and want to dynamically produce customised reports that are filtered based on the URI. What I am looking to achieve is a unique URI per X that ...
2
votes
0answers
33 views

Import site URLs to Google Sheet (and add new row each time)

I am trying to make a list of site pages that will automatically update in Google Sheets, while keeping the full history rather than overwriting older rows with new ones. I see two possible ways of ...
2
votes
0answers
685 views

Cannot retrieve data for some exchanges from Google Finance

Since last weekend, I encounter problems retrieving data from exchanges such as SGX and SHA. =GOOGLEFINANCE(SGX:ES3,changepct) =GOOGLEFINANCE(SHA:000001,changepct) The error message is; When ...
2
votes
0answers
76 views

How to set Audio alert in Google spreadsheet that triggers by a specific value?

Can I set an Audio alert in a Google spreadsheet cell that triggers whenever it changes a specific value? I want some thing similar to "beepnow" function which is using in MS Excel VBA.
2
votes
0answers
71 views

Formatting Google Sheet Dates Inconsistent

I have a sheet where various data is entered through a form. Dates are picked on the form using the standard date picker provided by Google Forms. Some dates in a column I can change the date format ...
2
votes
0answers
88 views

Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries) update

Thanks for a very useful script. Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries) I am trying to do the same thing, some of my rows have only one entry and ...
2
votes
0answers
20 views

Toggle named range window with a button?

I would like to know if it would be possible to toggle the different features in sheets with a script. What I am trying to do is to basically create a button that will show the Named Ranges side ...
2
votes
0answers
121 views

I created a button in a panel (dialogue window), when I push the button I'd like to appear a new panel (dialogue) window

I created a button in Google Spreadsheet. When I push the button a custom window appears. In that window I have two buttons, one to close and one to see a new window. Everything goes well except the ...
2
votes
0answers
248 views

How to change default row height of a migrated Google Spreadsheet

The old Google Spreadsheet has a default row height of 17. The new Google Sheets has a default row height of 21. After a spreadsheet is auto-migrated by Google to the new Google Sheets, the original ...
2
votes
0answers
162 views

How do I create a map from Google Sheet data that's in another sheet?

I'm trying to create a Google My Map based on addresses in a Google Sheet "file". This seems to work OK following the standard instructions, except that I can't seem to extract data on anything except ...
2
votes
0answers
321 views

Batch Hiding Function in Google Script

I wrote a function that iterates through every non-blank row in the sheet, checks the value of a certain cell, and then if that cell matches my criteria its row number is added to an array. I then ...
2
votes
0answers
265 views

Google sheets equivalent of self-referencing in Excel

This is, in my opinion, one big advantage Excel has over Google sheets is the self-referencing formulas. What I mean by that is: say in cell A1, I'm changing the value in there a,b,c. And according ...
2
votes
0answers
113 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
169 views

How to Modify Matched Cells Across Multiple Sheets with Google Script

My question is a fairly complex one, but I'll do my best to clarify exactly what I'd like to do and this requires some background: I have a Google Spreadsheet used for user administration in my ...
2
votes
0answers
76 views

Can't unmerge cells

I have a lot of data in a Google spreadsheet that depends on charts I spent ages trying to make look pretty. There is a stupid block of merged cells that won't unmerge, and when I try to ...
2
votes
0answers
753 views

Apps Script Webapp accessing Google Spreadsheet gets #VALUE! on every cell

I have a Google Spreadsheet with a couple of sheets that get data from other spreadsheets via ImportRange function and then calculate numeric values based on that data. Then, I assembled a code ...
2
votes
0answers
1k views

Sorting on a web-published Google Spreadsheet

I have a Google Spreadsheet that is published to the web and embedded in a website. This spreadsheet has data about hotel rooms, such as hotel name, number of rooms, date in and out, etc. I would ...
1
vote
0answers
9 views

Different bar types based on column value

Instead of having one column for each new "type" below, is there any way to achieve the same result using the non highlighted cells in the image below? To elaborate, I would like different bar types ...
1
vote
0answers
13 views

Pasting multiple formulas at once

I have a column of formulas (each created by concatenating several parts of a formula in cells to the left of it on each row), I want to be able to copy and paste these into a new sheet, whilst ...
1
vote
0answers
13 views

How to use dot as splitter in currency format with Russian locale

I want to write currency in the format: ₽ 3 812.23 But if I select Russia as region in the table settings and format the string ₽ 0.00 result formatted number like ₽ 3 812,23 with a comma, ...
1
vote
0answers
47 views

How to embed Google Sheet into Google Document and keep the Google Sheet format

How do I embed a Google Sheet into a google document and keep the Google Sheet format in the document? I have got this far with the script but it's not working function updateData() { var ssUrl = ...
1
vote
0answers
28 views

How to save a Google Sheets chart as an SVG or EPS?

By default, Google Sheets only lets you save charts as PNGs. This isn't good enough when I need high-quality images, or images that are editable after export. This quick tutorial provides guidance on ...
1
vote
0answers
15 views

How can I auto-format US and international phone numbers plus extensions of various lengths in Google Sheets?

I have contact data being submitted from a bajillion sources globally. I would like Google Sheets to format the phone number field specifically to accommodate international phone numbers and phone ...
1
vote
0answers
40 views

Convert jagged array to 2D array in Google Sheets

I have two columns in a Google sheet: an ID column with a single value per cell and Color column with an arbitrary number of comma separated values per cell. I'd like to expand the Color column so ...
1
vote
0answers
20 views

Dynamic dropdowns - for sales contact management exercise

I am trying to figure out a method in Google spreadsheets where I would be able to display information of contacts after I have selected 2 more criterias before. So imagine it this way: You see a ...
1
vote
0answers
32 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 = ...
1
vote
0answers
29 views

Google spreadsheets keep keys in sync between sheets

I have a google spreadsheet with a "master" sheet that has a list of rows (first column is a key and other are some data). I want to be able to have more sheets in the doc that would have the same ...
1
vote
0answers
38 views

Is there a way to get 'frozen' rows in an embedded Google Sheet?

I have a 400 row sheet embedded in a web page. I would like the header row to be frozen, as it is in the original sheet. So far I've been unable to discover any parameter to allows this, but also ...
1
vote
0answers
49 views

Identifying row records in different tables when scraping through ImportHTML query in Google Spreadsheet

I'm inserting two different tables successfully with ImportHTML. However I want to identify records in rows to which table it belongs. In this particular case (link attached) the player Higuain plays ...
1
vote
0answers
53 views

How can I decrease IMPORTXML refresh interval to 10 mins?

I have a couple of IMPORTXML’s fetching the prices for products, and I want the sheet to update the prices every 10 mins. I tried now() and a functions script but to no avail. What is the best way to ...
1
vote
0answers
90 views

How do I print a Google Docs document directly from Firefox?

Using Google Docs/Spreadsheet. If I open a Google Doc and select print (either via the menu, ctrl+p, or toolbar icon) from within Chrome or Opera it then opens a print preview screen where I can ...
1
vote
0answers
32 views

How can I get locations of thousands of places saved in Google Sheets?

I have a list of ten thousand places saved in Google Sheets. I want to get their location and sort them based on that. How can I efficiently get all of their locations? Can Google Maps API be of use ...
1
vote
0answers
53 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 ...
1
vote
0answers
9 views

Error message when using Trace Dependents code

I attempted to use your code to trace dependents in a google sheet found at this link: In Google Spreadsheet, finding what formulas reference a given value When I attempted to run it I received the ...
1
vote
0answers
19 views

How do I dynamically load all data from an XLS file into Google Sheets?

I have an automated service that places a new, huge (~6MB: ~20,000 rows) .XLS file into my Google Drive every day. I want to write a Google Sheets .gs script that will run once a day, and load all the ...
1
vote
0answers
28 views

How to not count blanks when using the COUNTA function

I am using the following formula to count the number of time a song name is used in a column: =COUNTA((IFERROR(FILTER('Form Responses 3'!E2:E, SEARCH(C2 , 'Form Responses 3'!E2:E ))))) It is ...
1
vote
0answers
13 views

match and lookup function on google sheets match different japanese kanji

Checkout this google sheets: https://docs.google.com/spreadsheets/d/1dw0v_64uN2lCyB7FxfoBmQ5d1OVIBEemxe5TtPaiUFY/edit?usp=sharing Despite the fact that those two kanji characters are completely ...
1
vote
0answers
8 views

Combining several workbooks into one

I have several workbooks need to merge into one. Each workbook has several tabs, it varies, some of the tabs are same name . I formats are same. In my master, I want to move these tabs. If tab name ...
1
vote
0answers
169 views

Google Spreadsheets - 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 ...
1
vote
0answers
36 views

Google Spreadsheet Filter() function randomly stops returning results

I've been having a reoccurring issue with Google Sheets. My FILTER() functions will randomly stop returning results instead saying "No Matches Are Found in FILTER Evaluation". I have to copy the ...