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.

0
votes
1answer
18 views

How to get the latest data group by the category where type 0 must not display

Based on the data below, I would like to get the latest date for each category where the type must not be "0" Here is the result that I would like to show
0
votes
0answers
4 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 ...
0
votes
1answer
19 views

Google Sheets Query Searching?

I'm trying to run a Query on some data that's being imported, and have it only Bring data over that matches the Email in the document. This is my code: =QUERY(IMPORTRANGE("SHEET ID REMOVED","'Form ...
0
votes
1answer
11 views

Suming values in a column if value in cell next to it matches another cell

I'm trying to create a sheet to maintain a small inventory of servers. I created a segment where each type of component will be listed at the top. Below it is a series of copy-pasted chunks which ...
1
vote
1answer
13 views

Catch New Rows in Existing Filter Views

I have a series of filter views that filter my Google sheet based on the values in column B. I have 200 rows in my sheet, so the range for each filter view is A1:C200. Often, new rows get added to ...
0
votes
2answers
28 views

Jump to next empty cell in specific column

I'm writing a script where I need to take in a number attached to a name and team, process it as # of Rolls essentially, and then paste the results one by one into a running log. Each team will have ...
0
votes
0answers
17 views

Google Sheets Query Not Importing All Data

I am using the google sheets query function to import data from one sheet into another. It is functioning properly and taking only the data I want. However, some of the columns from which data is ...
0
votes
0answers
21 views

How to create button that copy column and paste values in to another sheet

I`ve created google spreadsheet with several sheets. I need to create button with function that allows me to copy values of the 2 columns in sheet "test_air" and paste them in to sheet "Skaiciavimai"....
0
votes
0answers
9 views

Everyone in my organization call see *all* of my Google Sheets (spreadsheet) documents. How do I prevent default access from within my organization?

A few minutes ago, I hopped on a co-workers computer who was working with Excel for inventory tracking, trying to get her to use Google Sheets instead. After logging her in, I was (very) surprised to ...
0
votes
0answers
6 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 ...
1
vote
1answer
35 views

How to tag cells with multiple tags based off a list of tags?

Alright, there's a lot of similar Q&As out there but I can't get this figured out. I'm trying to categorize the cells in column A according to the list of keywords in column B. I've tried a wide ...
0
votes
0answers
11 views

I can’t attach a picture to a cell in Google Sheets

I have been trying for days to attach photos to this spreadsheet with no success. I feel I’ve tried everything. Can you please tell me what I’m doing wrong?
0
votes
1answer
11 views

DAY function does not work as expected in google sheet

I have a issue with Day function; I am extracting date from text string and storing it in a column; say Sep 30, or Sep 23; I want to see DAY of this date and put it in next column; something like ...
0
votes
1answer
32 views

How to trigger a script when a new text value is added to specific column?

I've built a sheet that generates messages for clients based on certain input data. The message appears in a cell in column B when all the required data has been entered. When a new text value is ...
0
votes
1answer
16 views

Inserting a row in Google Sheets does not copy the formula present in other rows

I am using Google sheets withe the following formula in one of the columns =ARRAYFORMULA(IF(ISBLANK(M2), "", ROUND(M2/G2, 2))) Whenever I add a new row anywhere in the sheet, the formula does not ...
1
vote
1answer
20 views

Use Google Forms to allow updating of old data and linking to correct record

I organise a small holiday programme which requires quite a bit of information about the children who attend (to ensure they are safe). This information splits into two types: Information that never ...
0
votes
0answers
58 views

Google Sheets macro for printing

I wrote the next macro, but isn't working. For example: I have a button on the sheet (invoerblad) for printing, the button must print the sheet (Printblad), and when the print action is finished, it ...
0
votes
1answer
75 views

Fine tuning a find and replace code

I have this code that works to find the word “Yes” and replace it with a 1. How do I also add in a line for it to find “No” and replace it with a 0? function fandr() { var ss=SpreadsheetApp....
0
votes
1answer
20 views

Open user database with separate spreadsheets

I’m trying to figure out a solution, most likely by combining multiple Google Sheets, for creating an accessible user database that also protects anonymity. Here’s what I have/need Right now, the ...
-2
votes
2answers
34 views

How to add checkboxes into cell using google sheets api v4

Is this is real or i just didn't find it in documentation? And how i can fill only column with checkboxes?
0
votes
1answer
73 views

How to calculate weighted average of averages in Google Sheets?

I have product review data. The reviews are out of 5 stars and I know how many there are (ie quantity of reviews). I'm trying to calculate a weighted average of the reviews because I'm trying to ...
0
votes
0answers
32 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 ...
0
votes
1answer
59 views

How to prevent duplicate entry in google sheets per row

So using the example data below, how can I prevent a duplicate entry with an informative message. The duplicate entry should be based on the row and not the whole sheet. So, if I was to add a second ...
0
votes
0answers
37 views

Automatically allocate deposits to invoices

I'm facing a difficult challenge (at least for me) with Google Sheet. I receive invoices all year long and I make deposit all year long that should offset invoices. However deposits values are ...
1
vote
1answer
27 views

Google Spreadsheet: Editing Status options in `Assignment tracker` template

I am using Assignment tracker default template of Google spreadsheet. The Status column has four default options Done, In progress, ... and Subject column has four options Maths, Biology, .... The ...
0
votes
0answers
24 views

Use arrayformula to allow vertical formulas to expand horizontally?

The following sheet (here) is using a vertical-range formula on some cells to calculate averages (vertically) every 3 rows. Once the end user will insert a new column, the newly created cells will ...
0
votes
1answer
42 views

Standard deviation in Google Sheets Query

I have data of jobs (A), their executions (B) and duration (C) in my Google Sheet. I have already calculated the average duration of each job with Query language: SELECT A, B, AVG(C) GROUP BY A, B ...
1
vote
2answers
24 views

How do I filter a table in Google Sheets to only show me the last 90 days of entries?

I have a spreadsheet I update daily that's grown to be ~1700 rows. I reset it every year, but I don't really need to see more than the last 90 days of entries. I can't use FILTER(), as I still need ...
-1
votes
1answer
25 views

Return the column header of the last >0 value in a row

I need help with a Google Sheets formula to return the column header of the last >0 value in a row (the yellow highlighted cells below are hard-keyed). Example:
1
vote
1answer
55 views

Google Forms - Responses Chronological Order

Can my form's responses populate in chronological order upon submission somehow automatically? I just did a test response with my co-worker and her 'order' was placed at the bottom, which I now have ...
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
votes
1answer
33 views

Unprotect ranges [duplicate]

I am trying to create a script to protect my formulas in sheets that co-workers will use. I have 10 workbooks with 26 sheets each, all are virtually identical as far as the the locations of the ...
1
vote
1answer
33 views

Google Forms to spreadsheet edit responses and new responses on the top

I've created a Google form and I want to be able to drag the rows into categories below it, but I want the new responses to always show up at the top. Instead, it follows the previous row. For ...
1
vote
1answer
61 views

How to use x-axis as data and not just labels for scatter chart?

I'm plotting 7 data point shown below. The column on the left represents my x-values (horizontal axis) and the column on the right represents my y-values (vertical axis). The first row labels the ...
0
votes
2answers
43 views

Conditional formatting if entry is final entry of the day

I have a Google Sheet in which I keep track of transactions, kinda like this: | Date | Funds | Running total | |---------|---------|---------------| | 5/23/18 | 10.00 | 10.00 | | 5/23/...
2
votes
1answer
15 views

Formula for Adding Text to End of Cell (contains URL) - How can I account for cells ending both with and without a “/”?

Working in Google Sheets, I have a column with a list of URLs. I'm trying to add a column that automatically populates the blog link (all sites end in "/blog"). However, my list of URLs are formatted ...
0
votes
1answer
50 views

Google script app delay without freezing spreadsheet calculation

How can I have delay on Google Script App function without freezing the spreadsheet calculation. sleep function freezing all spreadsheet calculation, and I have many calculation on the spreadsheet ...
0
votes
1answer
15 views

Need to trigger form submit when data is already populated in Google Sheet from another source

We have tried many thing to do a job basically we have a Google Sheet to collect information from Google Form once the form is submitted the data get inserted into the sheet and document studio start ...
1
vote
2answers
37 views

Formula for dates in Google sheets doesn't work when static time included

I'm trying to make a formula that takes a date formatted like this "10/7" and will spit out the date 3 days before it. So I make a formula =C79-3 and that spits out the date. However, I also ...
1
vote
1answer
50 views

Collated and separated results in one cell for QUERY results across multiple columns

In a Google Sheet I have the following formula: =CONCATENATE(QUERY(Summary!I4:U200,"Select I,U where S='"&A3&"'")) This currently returns each pair of results in sequence (with no spaces) ...
0
votes
1answer
23 views

Count unique tags

I’m trying to list and count all tags in my table. For some reason, anything after comma, is getting a white space before the word. So if the tag is found on the beginning of a record, it is seen as ...
0
votes
2answers
48 views

Need to cross reference values from 2 tables

I am trying vlookup. I get #N/A when in FALSE. When set to default (or TRUE), I get 80% correct and 20% wrong data. =VLOOKUP(C26;Image!A:F;1) There are no duplicates in A:F - double checked. ...
1
vote
1answer
67 views

Temporarily disable autosave in Google Sheets

I use autohotkey scripts for a lot of things in Google Sheets. It's basically scripted, automated keyboard input. A script may look something like this: Loop, 4 { Loop, 7 { Loop, %...
1
vote
0answers
74 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.
0
votes
2answers
17 views

Duplicating sheets with simple date from a named range

When I run the script to duplicate sheets with a date is give the tab a long date name Thu Oct 18 2018 00:00:00 GMT-0400 (EDT). I would like it to read in a 10/18/18 format.
0
votes
1answer
28 views

Is it possible to calculate in the background of a data validation cell?

Is it possible to calculate in the background of a data validation cell? I am creating a quite comprehensive spreadsheet that shall help me calculate dice pools in an RPG, but to not trouble you with ...
0
votes
1answer
51 views

Google Sheets intersection query

I'm trying to write an intersection query in Google Sheets that lists all the rows in sheet 1 that contain a cell in sheet 1, column A, that matches at least one cell in sheet 2, column P This is the ...
0
votes
1answer
21 views

Is there a column width bug in Google sheets?

In a Google spreadsheet when I change the column width to 25 I noticed it looked very narrow. So I saved it as an .xlsx and then opened it in Excel. There it shows a column width of less than 3 as I ...
1
vote
1answer
21 views

How to calculate years left in Google Sheets

This is the function I used and it work =YEAR(A1)-YEAR(B1), A1 is today B1 is the date in the future or past. The problem is that I want to round up year and months. This is the exaple: https://imgur....
0
votes
0answers
22 views

Reference Specific Row in Named Range within another Named Range

I'm writing a spreadsheet to keep track of a small business' financials. They operate a few Rooms for rent, and the structure of the document is made so that each sheet holds a year's worth of booking ...