Questions tagged [google-sheets]

Questions related to Google Sheets and interacting with Google Sheets programmatically.

0
votes
0answers
5 views

Select multiple rows, exclude rows 4, 5, 6

In Google sheets, I need to select rows, say, A2:A100, but omit A5:A10. I've tried ={A2:A4, A11:A100} and some FILTER expressions, but it didn't worked (
1
vote
1answer
10 views

Duplicating data in one cell as per repeat counter

I got into a bit of problem. I have data like this Date Repeat 7-Oct-2018 1 8-Oct-2018 1 9-Oct-2018 2 10-Oct-2018 2 11-Oct-2018 3 12-Oct-2018 2 13-Oct-2018 1 Now the ...
1
vote
0answers
18 views

Is there a way to execute spreadsheets.values.update without OAuth authentication?

I'm making a discord bot that will be open to the public to use, and it will work by updating a google spreadsheet with values given in a command. Unfortunately, from what I see in the API ...
-1
votes
0answers
13 views

Direct .csv download link from Asana?

I'm building a dashboard in G-sheets based on .csv exports from one specific Asana project. I would like to have a direct URL to access that .csv export so that I can use importdata() to dynamically ...
0
votes
1answer
21 views

Multiple IF(ISNUMBER(SEARCH excluding

On a cell I have a series of items (strings separated by commas) but wish to test if a specific item is appearing alone For example =IF(ISNUMBER(SEARCH(L5528,"apples,oranges,bananas")),"Stuff1"), ...
0
votes
0answers
28 views

How to load function following new script-loaded row in Google Sheets?

I'm a noob trying to pull user details in a web app, display as a row in google sheet, and email the user. I realize I can't run onEdit to run my working sendMail function if the edit was done via ...
0
votes
0answers
19 views

Enviar dados do formulário web para google planilhas [on hold]

Boa tarde Dev's. Estou criando um formulário html baseado somente em javascript-html-css. onde possuem 3 comboxes dependentes que popula seus itens através do Javascript. Esse formulário está ...
0
votes
0answers
25 views

How to add Emails as Editors of whole document using a Setup Sheet?

I'm trying to take my project from a stage to another, and I was able to make some good progress so far. I've got the following script that runs when the sheet called Setup_Protections is edited: it ...
1
vote
1answer
38 views

sendNotification continues to send emails if any cell in row with value trigger is updated

I'm using sendNotification to trigger an email when column 'G' has a value of 'All Components to DC'. But, the issue I am having is that when the column already has the data 'All Components at DC' and ...
0
votes
0answers
12 views

Problem with scraping data from the web. How to use XPath in Google Sheets correctly?

I am trying to create monitoring table where tracking number of the parcel from one cell concatenates with the tracking website URL and imports current status. On another site I use such construction ...
0
votes
1answer
16 views

How to use sheet Id in google spreadsheet API?

Google Spreadsheet document can contain some sheets. First is default and has it equal 0. Generally for any sheet there is address like this: https://docs.google.com/spreadsheets/d/(spreadsheetId)...
1
vote
1answer
21 views

Radio button in Google Sheets formula

So I've created sort of radio button chooser table with check boxes so I can pick which line I want and that brings the value from this line out to the result cell... this is the basic principle: ...
0
votes
0answers
25 views

Trying to pass form entry (from a sidebar) into a specific column/cell in Google Sheets via Google App Scripts

I've looked through many articles regarding my issue, but I was still unable to troubleshoot my problem. I'm trying to create a form in a sidebar where the entries would be concatenated with other (...
0
votes
1answer
15 views

Write auto sum (n+2)-th column in n-th column

I've been struggling for a few weeks to make an array formula which could automatically write in n-th column (of row 3) the sum of (n+2)-th column. Here is my spreadsheet. https://docs.google.com/...
0
votes
1answer
12 views

Google Sheets IMPORTXML with INDEX

I just encountered an odd behavior with IMPORTXML in Google Sheets. When I user the function IMPORTXML, everything seems to work as intended: But if I want only one field of the array returned from ...
1
vote
1answer
28 views

Extract first and last name from email address

I've seen a variety of this everywhere but nowhere that answers my questions specifically. I need to extract a first name and last name from an email address but also need to make the case proper with ...
1
vote
1answer
14 views

Google Spreadsheets: uppercase all words in a cell with more than three characters

I need to uppercase all words in a cell with more than three characters. Let's say the cell A1 is this is a text If I use this formula =proper(A1) I get this This Is A Text I need a formula to ...
1
vote
1answer
26 views

Write Google Forms Answer to Sheet as soon as it is entered

I have a Google Form with some questions for which the user has to choose an answer from dropdown. The answers in the dropdown lists are fed from a Google Sheet. Because I would like those dropdowns ...
1
vote
1answer
34 views

Borders and sorting formula called values

I have a number of importrange formula on a hidden store sheet. Various different sheets 'lookup' this data to display in a table. I have set up a simple script to draw borders when a new row of data ...
1
vote
1answer
23 views

Using =INDIRECT in google sheets with If statement

So I'm trying to create a Google sheet for a project where every 5 years we need to contact the users , I have set up in the first sheet a column for todays date - another column for date in 5 years ...
-3
votes
0answers
34 views

Google Script Javascript

I am wondering is there a way to tune the GetElemenetsByClassName command from within Google scripts. I want to get a link that is located using the text-steam class name and then get its href ...
0
votes
0answers
16 views

Ranking chart with negative values

Person Week 1 Week 2 Week 3 Week 4 ----------------------------------------- Dave 1 1 1 Sean 1 1 -1 Chris 1 -1 -1 ...
0
votes
0answers
28 views

“setActiveSheet” method not working on google sheets mobile app

I'm trying to write a code that changes the active sheet if a certain condition is met. To be able to run such a code in the mobile app version of Google Sheets, I wrote a function that gets ...
1
vote
0answers
69 views

autorun of script in google sheets

I use the Sheet() function in excel which is not supported in google sheets. I found a code snippet to simulate the function function SHEET() {return SpreadsheetApp.getActiveSheet().getIndex(); } I ...
0
votes
1answer
34 views

How does one reference another sheet using a string from another cell?

This doesn't seem to work for me: VLOOKUP(A4,INDIRECT(A4&"!")A1:Z40,5) What can I change? I get a formula parse error. I have fiddled around with quotes and such in the INDIRECT function, but ...
0
votes
1answer
49 views

Simple for loop, if statement and output message

I think this one is pretty simple, but I am new to this, so I am not sure where to go from here :) I have a Google Sheet, with some data (pretty large sheet). I want a script that check whether the ...
0
votes
1answer
20 views

Count the repetition of text in a list using google spreadsheet

I've got a list of text suppose from A1 to A{n} like these: Alternative Ambient Rock Metal ecc.. Then I've got a list of text in each cell divided by ";" So for example in the first cell I've got ...
0
votes
0answers
32 views

If I want to map an entire column of values to a string, is there a better way than individual index-matches/vlookups?

Say I have an unsorted column of data in Column A: 1 1 2 50 1 And I want to make a column B where each number is translated to a string 1 I 1 I 2 II 50 FTT 1 I Right now I am using ...
-3
votes
0answers
22 views

How to generate Table-B from Table-A?

See these example tables A and B, how could I convert A to B?
0
votes
1answer
54 views

Get cell by value

How do I get the range of a cell using its value and column? Say I have this column: [ ][ A ] [ 1 ][ hello ] [ 2 ][ world ] [ 3 ][ waaah ] [ 4 ][ waaah ] [ 5 ][ waaah ] Sheet: WALUIGI_SAYS_"HI"...
-1
votes
0answers
28 views

how to repeatedly call a list variable in python

from threading import Timer import threading def data(): iot = sheet.get_all_records() return iot t = Timer(10.0, data) t.start() I want to call iot list variable repeatedly, and I got ...
0
votes
1answer
38 views

How to create one spreadsheet for each row that also imports that whole row in Google Apps Script?

I'm a teacher looking to create individual spreadsheets for students based on an assessment. The students will be listed in column A, and I want each of them to receive the information from column A ...
1
vote
1answer
19 views

Import XML into Google Sheets

I am trying to import some data from a website which are updated monthly in google sheets. I am particularly interested in this number: "1910" see this picture. I typed: =IMPORTXML("https://ratings....
0
votes
1answer
18 views

How can I auto-sort by 1 column in Google Sheets

I'm trying to set up my Google Sheets document auto sorts when entering new data. Link to sheet: https://docs.google.com/spreadsheets/d/1h2-Gy67sTGKdMhG9GP-intB5fVdaJheZIcLo4Nto_Wg/edit?usp=...
-1
votes
2answers
39 views

write cell value based on an array of row numbers

I have the word "nice" that I want to write into a column. Normally I would get the range targetColumn.getRange(2, 7, sheetLR-1) and then just .setValue('nice') to write it into the desired rows of ...
0
votes
1answer
28 views

Apps Script : Timestamp is not copying properly

When I am copying timestamp from one sheet to another or in the same sheet only then it is not copying the time from the timestamp. It is only copying date. See the below image - Here is my code - ...
0
votes
1answer
7 views

How can I compute ARGMIN or ARGMAX in google sheets

How can I find the index of the maximum or the minimum (i.e. the argmax or the argmin, respectively) in a given one-dimensional range in Google Sheets?
1
vote
1answer
45 views

Send data to google spreadsheet

I need to send my form data to Google spreadsheet so I stumbled upon this link Submit an HTML Form to Google Sheets It all works fine. But when I change the name of the sheet it doesn't post data to ...
-1
votes
2answers
29 views

how to find combination of different rows in Google sheets

I have a sheet in which i have 6 columns, A-B-C are group1 and D-E-F are group2. I want to find out whether there exist a particular row for which group1's value is same as group2. ie. suppose group1 ...
-1
votes
0answers
11 views

Repeated data columns in countifs

I want to call a countifs function on sets of three columns to the effect of: countifs(a1:a,'other sheet'!a1,b1:b,'other sheet'!b1,c1:c,'other sheet'!c1)+ countifs(d1:d,'other sheet'!a1,e1:e,'other ...
0
votes
0answers
49 views

How to place this database (Ppto) on different sheets (MP04 / MP05)

Description: I have a sheet (Ppto) with a list of IDs, Credits(Cedente) and Debits(Receptor). I would like to move these transactions from this sheet to MP05, If Transaction amount and ID are equal....
0
votes
2answers
35 views

Custom Week number from Acquistion Date of each User

Is there a way to calculate Custom Week numbers that start from a user's first Transaction Date onwards? The Users (emailId) and TransDate columns may not be in a sorted condition as shown below: e.g....
1
vote
1answer
32 views

Compare two arrays, get rows numbers

I'm trying to break where I want to get to apart into smaller steps and questions so I can understand it better. I turned two columns (each containing email addresses, each from different sheets) into ...
0
votes
0answers
25 views

send chrome active URL from chrome extension to google app script(web app)

I want to send current active tab URL from chrome extension to google app script (deployed as a web app)at every 15 seconds to save it in google sheet. here is the code of chrome extension - ...
0
votes
2answers
31 views

Google Sheets - Return all cells based on nearby reference cells/multiple criteria

I'm attempting to create a table which shows averages based on multiple criteria of nearby cells. The best way to explain is with my sample sheet (with edit permission): https://docs.google.com/...
0
votes
0answers
15 views

ImportXML Googlesheetsby ID

Greetings I am looking to import information from a page on zillow URL: https://www.zillow.com/homes/for_sale/2088542183_zpid/40.684299,-74.220779,40.652106,-74.25009_rect/14_zm/ the URL is located ...
0
votes
2answers
43 views

get first column in array

I have a sheet and need to get the values (email addresses) of a certain column, which is C in this case. Let's assume there are three emails in the column. The log gives me: "[[email1], [email2], [...
-1
votes
0answers
19 views

Google Apps Script Remove Duplicate rows

I have a script which currently splits one cell from form data which is working great. I simply need to remove any duplicate content before the script runs and splits out the data from the cell. They ...
0
votes
0answers
11 views

import data from web to google sheet with user authentication

I want to import data from a specific web page to google sheet that requires user authentication. I tried with importhtXML and XPATH but doesn't work. I read that I need a script but don't know how ...
0
votes
1answer
28 views

Script for BOM with multiple same item that need to be merged

I am new to coding, and working with google sheets and trying to write a script (or some other combination of formulas) that will spit out a BOM on a new sheet tab with all qty merged (we have ...