Questions tagged [google-sheets]

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

0
votes
0answers
3 views

Using a Vlookup formula within an Arrayformula

I'm using the following formula to search a column for "Yes" and it works fine in the cell: =VLOOKUP("Yes",INDEX(AH:AH,ROW()):INDEX(AI:AI,ROW()+30),2,FALSE) However my sheet is over 20000 rows and ...
0
votes
0answers
11 views

Google sheets : CreateMenu -> addItem refer to function in another script file?

I am trying to create custom menu in Google sheets using Google app script. I found the following code from google documentation and it is working fine function onOpen() { var ui = ...
0
votes
1answer
8 views

Google Script: Updating a Single Cell with an IMPORTDATA Formula

I have a single IMPORTDATA formula in Cell A1 of a Google Sheet named "test-r" that I would like to be able to update the cell on a specific interval -- anywhere from seconds to hours. After a good ...
0
votes
0answers
5 views

Google scripts code running slow when batching functions

I use a cell to help trigger functions in a spreadsheet I made ie: when cell A13 = "loading old orders" do this It should run for around 4 mins then end then it repeats this until the code finds "...
0
votes
0answers
9 views

How to download the content of the web page to google sheet, using importxml

I need to download the contents of a query from the following webpage https://registro.br/tecnologia/tools/whois/?search=plasticosduvale.com.br I've tried for hours using importxml, but I can not ...
0
votes
0answers
19 views

Saving Spreadsheet to Folder in Google Sheets API (Python)

I am working with the Google Sheets API in Python but am having trouble finding out how to save a newly created Spreadsheet to a specific folder. As of now all it does is populate my drive without the ...
0
votes
0answers
14 views

Grab the folders of different parent folderids and display output in google sheets

I would like to grab all the folders under several parent folderids. I am using the code below but think it is missing something. function searchFolders() { var sheet1 = SpreadsheetApp....
1
vote
1answer
14 views

How to join mimic many to many behavior in Google Sheets?

I have 2 tables in a Google Sheets file. And I need to join the tables to make a calculation: Example: In this example, I need to get the total score or sum of all reports a team is associated with. ...
1
vote
2answers
28 views

Subtract two dates and times, and find if the later value exceeds 2 hours

Writing a Google sheet, and need the formula to evaluate two dates and times, and determine if the later value exceeds 2 hours and a day. IE, In - Out 1/1/19 2:00 - 1/1/19 3:59 1/1/19 2:00 - 1/1/19 ...
0
votes
1answer
20 views

Is there a way to add and manipulate check boxes in a google sheets using python

I need to a way to add, check and uncheck checkboxes in a google sheets sheet via a python api. Currently I am using Gspread to interact with the sheet however I don't believe that it supports this ...
0
votes
1answer
20 views

How do I pull the contents of a thread matching search criteria into a Google sheet?

I currently have a Google Apps Script running a spreadsheet that pulls the correct Email I am querying for. However, I can only get the original email body contents, when I am actually trying to pull ...
1
vote
1answer
13 views

What alternatives do I have when encountering IMPORTDATA maximum size error in Google Sheets?

I'm using the IMPORTDATA formula to populate a CSV file. I'm encountering an Error. The file size is 8MB so I think that may be the issue since I've previously used this formula with no problems, do ...
1
vote
1answer
14 views

List Sheet ID's in column using Google Scripts

I am trying to get the sheet ID's to list in a column on the master sheet. I have 15 sheets and I want to get the ID's of each to show on Master in column A1:A15. I figured out from here how to get ...
1
vote
1answer
13 views

Transpose Unique Values from Multiple Columns

I'm trying to generate unique values in D1:F but my formula is not working. For example, row 1 has A B C milk milk 44 the output should be: D E milk 44 here is my ...
1
vote
0answers
12 views

How would I identify all blank cells in a column, and sync a separate tab to the entire row of data for each blank space in that column?

Sheets newbie here. I am working on a multi-tab sheet, where I'm trying to identify in real time all the blank cells in a column, and for each, grab its respective row, and put it into a separate tab. ...
1
vote
0answers
16 views

Accessing data in Google Sheets for fulfillment with Dialogflow

I'm trying to create a custom Google action to turn on machines in a facility and log who is using them and for how long. How do I access Google sheets without using a service account? So far, I've ...
0
votes
1answer
3 views

Dynamic dropdown option load dissapears html modal dialog

My Html form has dropdown which load dynamic options using api request. I'm using google HTML Service to link google app script with html file. My problem is my modal dialog getting disappear as soon ...
0
votes
1answer
21 views

How to get information on hidden rows from Google Sheet API without Google Apps Script

I am using the Google Sheets API V4 to retrieve its data in JSON like below. That includes all rows, even the ones currently not shown in the Spreadsheet UI because of a filtering in one of the ...
0
votes
1answer
12 views

How to VLOOKUP the second column from an IMPORTHTML result

I am hopeful this simple question has a simple answer. I am using the IMPORTHTML function to import a table to my spreadsheet. I want to find a particular result from that imported table. However, ...
2
votes
1answer
16 views

Google Sheets - Date Format Display Issues

I'm currently building dashboards in Google Sheets and have run into a consistent issue with dates being displayed as a number opposed to a date format. EX: 43626 opposed to 6/11/19. This data is ...
1
vote
1answer
17 views

Google Sheets Formula Remove comma delimited list from string

I have prepared the following google sheet: https://docs.google.com/spreadsheets/d/1OU_aLgaIdDD2-pBNSV0y1d5B-YDAZeKbWb1PVtdkGZA/edit?usp=sharing In cell B3, I want to show the contents of B1, after ...
0
votes
0answers
24 views

How do you copy more than 1000 rows to a new Google Sheet?

I have some code that pulls a range of data from a source sheet in another file and copies it to a new sheet in the current file. It used to work but now that the source range is over 1000 rows, the ...
0
votes
0answers
13 views

How do I change majorDimension value for a particular range in batchGet

I am trying to pull information from google sheets but there is one problem where I want to pull different ranges with different dimensions. For Example: /* This is something I want to achieve */ ...
0
votes
2answers
15 views

Script to hide a google sheets row doesn't work consistently

I am just learning to play with scripts and cannot get a script to hide and show a specific row to work on the sheet I want to apply the script to. I have a google sheet I created as a sandbox where ...
0
votes
0answers
14 views

Mail Merge and sendEmail execute perfectly alone. How can I make them work together?

Prefacing this question with this: I am a novice programmer. This is my first exposure to Google Apps Script and I'm sort of at a loss as to what to do. Apologies if this has already been asked before....
1
vote
1answer
20 views

Google script - Hide/Unhide google sheet based on checkbox on another sheet

I"m pretty sure I'm doing something wrong in the code, as to why it's not functioning the way I want it. Firstly, here's the code: function onEdit(a) { var sheet = a.source.getActiveSheet(); var aa =...
0
votes
1answer
11 views

Show svg hosted by a local server in Google Sheets

I'd like to show a locally hosted image in a google sheet. Simply using =Image('http://this.is.local/image.svg') does not work, as the image will be loaded through Google Image cache proxy. As the ...
1
vote
1answer
20 views

ArrayFormula - If cell contains match, combine other cells with TEXTJOIN

I have a Google Sheet that contains names of characters, together with corresponding values for the group name, "selected" and attack power. It looks like this: Sheet1 | NAME | GROUP NAME | ...
3
votes
0answers
42 views

How to efficiently move values from a column to a different column without overwriting values?

I'm trying to move some dates from one column to the next neighbouring column. I want it to only move cells before a specific date and until it reaches an empty one. I've tried using the filter ...
0
votes
0answers
8 views

Extract email contents and export csv attachment to sheet

Hope you're all doing well. There is an e-mail I receive periodically, which contains a message, which is not very important and also contains a zipped CSV file which I want to extract its contents ...
0
votes
1answer
8 views

Only show a portion of a live Google sheet

I want to embed a live Google Sheet inside of new google sites. works fine but I want to hide/remove the title row and column headers and just show the workspace area I have tried the following ...
-5
votes
1answer
40 views

Send Email from Google sheet as a table without using sheets convertor

Please check the spreadsheet below: spreadsheet https://docs.google.com/spreadsheets/d/1QFPO4bQfYPM4rRJ_6PYxUrYsFgVeUFx89_nZ1mNaLew/edit#gid=0 The script that I'm currently using is working fine ...
0
votes
1answer
21 views

I can't get the parsing-options of ImportJSON to work

=ImportJSON("https://api.coinmarketcap.com/v1/ticker/bitcoin") works, but: =ImportJSON("https://api.coinmarketcap.com/v1/ticker/bitcoin","/name","noHeaders") doesn`t. I tried different Versions of ...
0
votes
1answer
25 views

Check date and insert sheet with columns and rows

I am trying to automate a report at work. Data is fed into Google sheet from google form, at present I have automated part of the process see code below. var sheets = SpreadsheetApp....
0
votes
0answers
8 views

Take the value of a cell corresponding to the match of another cell value in multiple columns : put all in an single column

I have multiple columns from P2:P to AT2:T with on the first row the days and some numbers or blank cells in all the rows after. I want to display on 2 columns : one with the list of the numbers ...
0
votes
0answers
9 views

Change image text in Google Sheets' cell

How do you change the text of an image inside a Google Sheets' cell? We use google sheets for our orders as shown in the following image. This is confusing for the other party. It would be better if ...
0
votes
1answer
18 views

Google Sheets query function returns 0 rows when using ORDER By

I am able to use the QUERY function to get rows from another sheet but if I try to use ORDER BY I get nothing. Oddly, if I do ORDER BY with DESC it does work. My example sheet is at https://docs....
0
votes
1answer
33 views

Efficient Way of sending Spreadsheet over email using GAS function?

I am creating an addon for Google Sheets that my local High School's volunteer clubs can use to keep track of their member's volunteer hours. Most of the code is done and works very nicely, and I am ...
0
votes
0answers
20 views

Dynamically prepopulate and lookup values in Google Form from Sheets

I'm trying to create a Google form for our small business to help capture data from our field workers on the tasks they are completing. We've previously used other systems which have provided their ...
0
votes
1answer
22 views

google apps script sidebar form Send button not working

I'm creating a google add on for sheets. The sidebar I'm working on is intended to be sort of help ticket submission, but way before I can develop that part of things, I'm not getting the submit ...
1
vote
1answer
18 views

VLOOKUP matching 3 columns with 3 others in Google Sheets

I have this formula in D1: =arrayformula(IFERROR(VLOOKUP(A1:C,H1:K,{4},0))) I'm trying to match columns A,B,C with columns H,I,J and pull data in column K. The formula is currently not matching ...
0
votes
1answer
24 views

How to create a filter formula as an arrayformula

I have multiple columns that depend on this formula. I have instances were an employee can have multiple assignments for the same Project and I use the formula to consolidate the rows and add the ...
2
votes
2answers
48 views

What is the RegExMatch equivalent in Google Apps Script?

How do I get the equivalent of regexmatch in apps script? Spreadsheet example: A1 = "ThisIsA2018Test" B1 = "IsA(\d){1,4}Test" REGEXMATCH(A1,B1) returns true... How would I match this using Google ...
-1
votes
0answers
13 views

Identically Formatted Data Shows Up Differently in Query [on hold]

On one sheet, I have input raw info. Some of the cells in column F and column G are stacked numbers, meaning that they are two or more numbers with a hard return (alt+return) between each. When I ...
0
votes
0answers
26 views

In a Google SpreadSheet embedded in an iframe how to open hyperlinks inside the iframe instead of opening in new tab?

I embedded a report made in Google Sheets in a web page. The report is very big, and has lots of pages so I added hyperlinks to ease navigation for the user. The problem is that when I publish the ...
1
vote
0answers
41 views

How to send an email with a formatted html table and message?

I am trying to send an email contains a fixed message and below it a formatted HTML table which gets the range values only if the total column "row[23]" is not empty, which means he's on shift. ...
0
votes
0answers
44 views

Google Sheets Macro to Copy a Sheet, Rename the Sheet, and Change a Cell on Each New Sheet [on hold]

Need help finishing 2 scripts to run in Google Sheets that will copy a sheet, rename it, and change a cell value on each new sheet. One script should create 1 sheet, the other script should create 30 ...
1
vote
2answers
31 views

How to ensure onEdit functions do not miss-fire

I have a google sheet where the user scans in barcodes of products. I need to record the date and time of when the product is scanned, so have built an onedit script to do just that. However it is not ...
1
vote
1answer
13 views

Google Sheets equation that doesn't go below zero

I have an equation that looks like this in Google Sheets: = (x - 500) ^ 0.38 The problem is that any value of x under 500 results in a cell value of #NUM! since zero to the power of 0.38 is not a ...
1
vote
1answer
20 views

If value in column A of Sheet1 appears twice in column B of Sheet2, highlight value in Sheet1

Conditional Formatting - If a value in column A of sheet 1 appears twice in column b of sheet 2, highlight the cell in sheet 1 I've been messing with the indirect modifier to see if I can get this to ...