Questions tagged [google-sheets]

0

votes
0

answer
3

Views

Is there a way to remove protection in google script for a user?

I need to remove protection for a user that has access to certain cells but needs all the cells when the script is running. After that i then need to protect the sheet again apart from the protected cells.
Robert Hall
1

votes
2

answer
183

Views

QUERY function with one result returns value beneath the formula cell

When I use the =QUERY function in Google Sheets, it returns the value in the cell beneath where I typed the function. I want the returned value to appear in the cell I typed the function. Currently, I have =QUERY(Data!$L:$N,'select avg(N) where L = ''&CONCATENATE(B$1,$A2)&''') in cell B2, and it is...
John Doe
1

votes
1

answer
646

Views

InvalidArgumentException JKS not found sheets API

I am working on an android app that needs to interact with drive/spreadsheets. I was using the google drive for android API, but not only was it way too convoluted to figure out, it never worked right. So I switched to the REST API. I tested the app in plain java before trying to add it to android....
mirGantrophy
1

votes
3

answer
827

Views

How to use substitute function with query function in google spreadsheet

I am trying to use substitute function inside a query function but not able to find the correct syntax to do that. My use case is as follows. I have two columns Name and Salary. Values in these columns have comas ',' in them. I want to import these two columns to a new spreadsheet but replace comas...
Rinaz Belhaj
1

votes
2

answer
340

Views

UPS Tracking On Google Sheet does not work

I used following formula get UPS live tracking feed and it works fine until yesterday. I think UPS has updated their site and this formula does not work anymore. Any idea or suggestions for how to get the tracking update from UPS? =Index(IMPORTXML('https://wwwapps.ups.com/WebTracking/track?track=ye...
PrasadD
1

votes
2

answer
150

Views

There was an error during the transport or processing of this request. Error code = 10, Path = /wardeninit

I'm trying to pass an object (contents of a sheet row) to an apps script template. You can see the row in the screenshot. my function in apps script contains: var sendableRows = rows.filter(function (row) { //ONLY CHECKED ROWS. return row['Index'] == true; }); var sendableRow = sendableRows[0]; Lo...
user61629
1

votes
3

answer
29

Views

Difference between two dates expressed as years, months, days (in one column) in Google Spreadsheets

I have a set of columns set up in Google Sheets that display the difference between 2 dates using the DATEDIF function: =DATEDIF (AS2, TODAY(), 'D') (number of days passed today since a certain date) =DATEDIF (AR11, AS11, 'D') (number of days passed between two certain dates) The values are represe...
Maeldun
1

votes
3

answer
26

Views

A formula to count the number of primes in an certain range in Google Sheets

I am a beginner in Google Sheets formulas, so I do not know anything in this area. I need to know a formula that returns the number of primes. I'm very sure primality testing algorithms are not very simple (I've never made one before).
Mr.YouKnowWhoIAm
1

votes
2

answer
33

Views

Way to overwrite specific column data based on new form response?

I have a Google Form (form 1) which a sales person fills out once a sale is complete. This form gives an option to upload customer documents eg. Copy of Passport, Proof of address etc. These are optional since the customer may not have them at the time of sale. These uploads are tracked via Google d...
NeverPhased
1

votes
2

answer
57

Views

How to implement the makeApiCall() method after migrating Google Sheets API v3 to v4 with Oauth Authorization in Javascript

Google has build v4 of the Google Sheets API but the documentation of this is still at v3. There are no full examples of how to implement a simple OAuth authentication with Google Sheets API v4. I see a migration guide here and I also see a partial example here But a full example is nowhere to be fo...
Elnoob
0

votes
0

answer
3

Views

How to copy the value of one sheet to another sheet Google sheets Script

I have a customer sheet and a invoice sheet When I go to the invoice sheet D12 and I type the invoice ID that's located in the customer sheet, then I get all the data from some vlookups. But what I want is this: I click on a cell in the customer sheet where the invoice number is located in that row....
Ralph Schipper
1

votes
0

answer
9

Views

How to expand my function in all the checkboxes in the document row by row?

my function works well for one row of my sheet (row 4). But i want to check all the row in column 2 and 7 where checkboxes are. I thought about a loop but i would like to use arrays for a better code and more efficient. function showAlert(activcell) { var ui = SpreadsheetApp.getUi(); var result = ui...
jean74
1

votes
0

answer
6

Views

How to hide rows if cell is not blank

I'm still very new at this so it's probably an easy fix, but I need to hide a row if Column D is NOT empty. Column D will have multiple dates so I just need to hide it if anything appears in Column D. I also want it to apply to all tabs within the spreadsheet. Any help would be much appreciated! fun...
Amanda Boyter
0

votes
0

answer
3

Views

How to insert a checkbox in a given cell when one is deleted

I am looking to create a script that replaces checkboxes in given columns when they are deleted. Below is an image depicting my setup. I would like it so when a row is deleted only the columns with checkboxes will be replaced with checkboxes again. Image of sheet I am looking to replace only the lab...
brendann755
0

votes
0

answer
11

Views

How to fix the error http 400 “unable to parse range”?

I'm trying to connect python to Google Sheet. I followed the python quick start page : https://developers.google.com/sheets/api/quickstart/python . I want to write to my google sheet with python but got an error the error i got was https://sheets.googleapis.com/v4/spreadsheets/1VToTIofV5MNKJa-9nZ0J...
macklabsc
1

votes
1

answer
8

Views

How to use COUNTA in a Google Sheets array formula

I am trying to do an array formula that counts the errors in some columns and if it finds it scores the row as failed and vice versa I tried this formula but it didn't work =arrayformula(IF(ROW(B:B)=1,'Transaction Score',IF(LEN(B:B)=1,IFERROR(1/0),IF(COUNTA(CV:CW,DJ:EC,ED:EJ,CI:CO,BO:CH,BA:BB,AM:AT...
Omar
1

votes
2

answer
18

Views

How do I select a random word within a cell?

I want randomly to select a word from a cell that is generated by a form field using the 'paragraph' answer option.
adam
1

votes
1

answer
88

Views

CacheService.putAll ignoring keys over a certain value

I have a problem with putAll() method of google cache for script. The method seems working but only with first (~100) pairs In my script i have to cache a big number of rows (~2500 * 3), each row in a different key, and I found malfunction in cache service. To identify the problem I wrote a simple c...
brazoayeye
1

votes
3

answer
102

Views

Google Apps Script: setFormulas and rangeList

I'm trying to setFormulas over a range of non-contiguous cells. I need a formula (they're all different) set every 30 cells in a single column (c). It works to setFormula for each cell, but creating 56 variables seems unnecessary. I can get the formulas but not set them as intended. I also tried us...
N.O.Davis
1

votes
3

answer
56

Views

How to return element out of array using filter method?

This code returns an array of arrays: var values = [[1],['a'],,['b'],[''],['c']]; var noBlankValues = values.filter(function (el) { var v = el != null && el != ''; return v; }); console.log(noBlankValues); How should I change it to get an array like [1,'a','b','c']? I tried this but with no luc...
F. Vosnim
1

votes
2

answer
62

Views

Case-sensitive formula to perform a COUNTIF for number-letter combination of IDs

We have 15 to 18 symbols long IDs that are a mix of letters and numbers. On the regular, we need to perform a COUNTIF() to determine the exact number of unique IDs. The issue is that sometimes the only difference between one ID or another is whether the case of one letter is upper or lower. COUNTI...
I. Я. Newb
1

votes
1

answer
40

Views

Using google spreadsheet as a database for a tumblr theme?

I am currently using tumblr as a website for my club. On tumblr there is a page, where every club member has a 'profile card' with their name, description and profile image. I wondered if there is any way to create a google spreadsheet with the members' information and use the spreadsheet as a datab...
Didi Kong
1

votes
4

answer
35

Views

Getting the unique values and counts of them in a column of comma separated values in Google Sheets without using JOIN?

I have a column with a bunch of ingredients lists in it. I'm trying to figure out how many times different individual ingredients appear. There are 73,000 rows. The answers on this question works for a small amount of data in Google Sheets. Formula is =UNIQUE(TRANSPOSE(SPLIT(JOIN(', ';A2:A);', ';FAL...
Kyle Pennell
-1

votes
0

answer
16

Views

need your smart people google sheets script code

when I wrote'copy' the code. it moves the line from one sheet to a different sheet. but my question is that I want it to go on the next line on the new sheet. it goes to the button on the page now. can someone help, please? function onEdit(e) { // moves a row from a sheet to another when a magic val...
Leon Korn
0

votes
1

answer
12

Views

Connecting Spreadsheet to BigQuery

I want to connect a Google Spreadsheet to a new BigQuery table that populates and update the data automatically. I'm using this tutorial to do the setup. My problem, I had to configure each column manually and the table went empty so I have to query it to another table to bring the data. I'm not exp...
Igor Souza
0

votes
0

answer
5

Views

Alert Pop-up three days before today of a date column in googlesheets

I'm new to Google App Script and need help. I'm trying to have a pop-up box three days before the date of all my dates in column (B). Please Help Thanks
mohammad shafiei
0

votes
0

answer
4

Views

Automatically sort rows when data is changed

In my google-sheets I have 2 columns , A and B, which I would like to sort descending by values of Column B. This I know can be done using 'filter'/'sort' options in sheets. This works as expected but this is a manually step that should be repeated every time data in column B changes. I would like a...
Kasper Odgaard
1

votes
0

answer
32

Views

sheets add-on giving 1-hr trigger error, even when there are no triggers installed

I wrote my first google app script with simple menu & sidebar; it works fine from script-editor but when I publish it as a 'sheets add-on' then 1. one of the textbox's in sidebar is missing 2. sidebar's submit/cancel buttons are not working Further more, on executing other menu items ... I keep on...
Deepak Kumar
1

votes
1

answer
222

Views

getActiveCell() wont work for me on google scripts

I've been trying to do a quick code for someone that will change the background color of a cell in google sheets when they input a specific phrase. The code runs fine with no errors but it doesn't produce anything on the spread sheet. Any help would be amazing. This is the code i have: function Ch...
carlosaicrag
1

votes
0

answer
342

Views

I need to make multiple copies of google sheets, including sharing and permissions

I have weekly, hour by hour production logs for many different machines. I need to make 52 copies at a time, keeping permissions intact. I did find a way to make copies, but I lose the permissions on the protected ranges. This is how I made the copies- function copyDocs() { for(i=0; i
Michael Lake
1

votes
1

answer
124

Views

Google apps scripts set cell formulas needing row number for a row added via appendrow

I have a google sheet that is populated with activities via a google form. One of the fields is an activity date field. Once done, the activity is moved to another sheet for reporting purposes via appendrow. var cells = [activityDate, title, completionDate, ...]; destsheet.appendRow (cells); To ai...
BrianScheffer
1

votes
1

answer
164

Views

passing multiple arguments to addCallBackElement Google Script

Is there any way to add multiple arguments to an addCallBackElement() function in Google Scripts? I want to send information about what the user typed into a given text box, as well as an integer. Can I send both of these? If not, is there any way I can see what the user typed into a textbox without...
Ethan Goldberg
1

votes
1

answer
41

Views

how to prevent manual changes (that are not written by the script) on a sheet?

I have a Google sheet where I need data to be entered on it only by script and I need to protect it from manual adjustment by protecting the rows already filled by script, the case is we are a team and a script logs all the changes every team member does in a row on a separate sheet (ChangeLog). Th...
Mostafa Abusamra
1

votes
2

answer
144

Views

Results from IMPORTHTML formula in Google Sheets if starts with 0. the 0. is skipped

I want to obtain in a Google Sheets cell, the value of a cryptocoin at certain date. So I have, for example, the following url which shows the prices (open, high, low, close) of the Waves criptocurrency at 15th of December: Link So with =INDEX(IMPORTHTML('https://coinmarketcap.com/currencies/Wave...
Egidi
1

votes
1

answer
64

Views

Combine 2 queries (different columns from 2 different sheets) and filter based on matching results

As usual, I have set a goal, way beyond my skills... I need to get data from 2 sheets, One has a lot more entries than the other (a master list I guess you could say). Any entry in the smaller sheet will always have a matching entry in the Master, but not necessarily the other way round. I have wri...
David Tonkin
1

votes
1

answer
46

Views

How to make for loop script more efficient?

I'm very new to script writing in general, especially in GAS, and want to make sure I learn good habits. I wrote a for loop script that, in essence, does the following: Read through column A starting at row 2 If cell above current cell in for loop is the same value, then clear the contents of the ad...
Bcos
1

votes
1

answer
22

Views

Getting certain key of returned JSON after using IMPORTDATA function

I am calling this url using the =IMPORTDATA() function and I am getting the following JSON back: {'ETH':{'BTC':0.002616,'USD':1.13}} How can I visualize only the value of the USD key in the cell?
Egidi
1

votes
1

answer
49

Views

Performance issue: 1 simple script tested on 2 spreadsheets, execution time differs (from less than 1s to 7s)

I tested a simple script on 2 different spreadsheets and the execution took less than 1 sec for the first spreadsheet and about 7 sec for the second spreadsheet (even though it's the same code). Here is the code, very basic (just retreiving a value in a sheet), used on the 2 spreadsheets: function t...
Julien
1

votes
0

answer
377

Views

How to Aggregate Strings Using Google Sheets Pivot Table Calculated Field

I have a Google spreadsheet in which I recorded text evaluations of various teams made by individuals. The sheet, named Data, looks like this Col A Col B Col C Rater | Team | Feedback ------------------------- r1 Jim | A | Great r2 Jane | A | Sucks r3 Kate | A | Fabulous r4 J...
idomeneus
1

votes
0

answer
128

Views

How to access data form Google sheets html sidebar

I'm trying to access the values in my custom sidebar menu. Been trying to find the solution for too long. My end goal is to create a new sheet in the active spreadsheet based on the response from the sidebar. 'ReferenceError: 'document' is not defined.' var assessmentName = document.getElementbyId...
Chris Shortreed

View additional questions