Stack Exchange Network

Stack Exchange network consists of 175 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-apps-script]

For questions about using Google Apps Script to extend Google applications

1
vote
1answer
19 views

Google Apps Script, MimeType.MICROSOFT_WORD will list only .docx

I am using a Google Apps Script to list Word files in a folder, however this will work only for .docx files, not .doc. Any idea? var folderID = "ID#" var sourceFolder = DriveApp.getFolderById(...
0
votes
1answer
34 views

Get the name and link file based on Folder ID

I created a script to get the name and link file based on Folder ID. The file name and link are displayed on sheet 'Get_Link'. File spreadsheet The script that I wrote like this: function ...
0
votes
1answer
38 views

Running script to replace “null” with nothing in Google Sheets

I tried to do this through a macro but I'm unable to have it actually record. I'm not a coder so I've been recording macros and using the code from there to create an add on. I need a script that can ...
0
votes
1answer
38 views

How to trigger a script in Google sheets when a value changes

I try to fire a script when a value in a certain cell or range of cells changes. (it's not changed by the user, so I can't use onEdit) I've tried to use onChange, but this doesn't fire my script at ...
1
vote
2answers
71 views

Send a google form result to an email

I need help with the google form. The problem is this: I want to receive an email with its results for every user who completes the form. For example, if you had a form that required email, name and ...
0
votes
2answers
26 views

Google Sheets API call creates scientific numbers that I cannot then run calculations on

I am using API in Google Sheets to call prices of cryptocurrencies vrs Bitcoin from URL: https://api.coinpaprika.com/v1/tickers?quotes=BTC In some cases I am getting scientific notation instead of ...
0
votes
0answers
19 views

Two timestamps referencing different columns in one google sheet? [duplicate]

I'd like to have a timestamp occur in column 6 when an edit is made to column 1. But another timestamp to occur in column 6 when an edit is made to column 8. I would be open to having the 2nd ...
0
votes
0answers
10 views

Google Apps script to read through folder of slide presentations, compare to template, and import updated slides into a presentation

I teach high school using Google Classroom. I have students used Google Slides to keep track of their projects. I create one template Slides Presentation and GC automatically creates an identical ...
1
vote
1answer
10 views

Bulk permissioning of Team Drives

I've been looking through the documentation but am coming up empty handed. How can I assign permissions in bulk across multiple team drives? For instance, a new hire needs to be given Commenter ...
0
votes
1answer
31 views

How to automatically add new row in Google Spreadsheets

I want to automatically add row in Google Spreadsheets when I type information in row 5 by Google script. ]1
0
votes
0answers
17 views

Missing Google Apps Script trigger

Receiving the below email notification from google apps script for a trigger I used recently. I'm done with the trigger now and want to stop it from firing. When I open my scripts dashboard to ...
1
vote
1answer
46 views

Google Sheets count columns in a merge

Is there a formula function in Google Sheets to count the number of columns in a merge? I could assign a named range to the merge to make it easier.... For instance: see the highlighted merged cells ...
0
votes
1answer
34 views

Re-using a google form sent data, avoiding entering all data

I am building a Google form to manage summer camp subscriptions. Each request must enclose parents and child data. The problem is, if a family wants to subscribe more than one child, the process must ...
3
votes
1answer
32 views

Can I limit how frequently an 'on open' script runs?

Basically I have a bunch of functions that run on Open, but if possible I'd like to limit them to only run once per 60 minute period. Just so that if a user accidentally refreshes or closes the sheet, ...
0
votes
0answers
15 views

Is there a way to check a user's permission in Google Sheets?

I am trying to find information on this but haven't really been able to find an answer. Is there a way in Google Sheets to, through scripting, check a user's permissions for the sheet? My ...
0
votes
2answers
89 views

Custom SHEETNAME function not working in Arrayformula

I have been successfully deploying the sheetname function outlined in Display sheet name in Google Spreadsheets for a couple of years. It has allowed me to have several sheets collecting information ...
1
vote
1answer
50 views

Inserting date automatically when a tick box is ticked

I have a simple sheet that has two columns with tick boxes in them (columns 5 and 8) The goal is when the box is ticked the date and time are inserted in the column next to it. I have got it working ...
0
votes
1answer
21 views

Adding numbers to a cells formula?

Let's say A1= X+Y Now I'd like to add +Z with a script so I end up with A1= x+y+z. I know how to copy it, or overwrite it, but not sure exactly how I would add to it with a changing formula in A1. ...
0
votes
1answer
67 views

ImportXML not stable in Google Sheets, how can I improve that?

I have ImportXML in my spreadsheet. I am tracking the Alexa ranking of a list of websites. Here is the formula: =HYPERLINK(CONCAT("www.alexa.com/siteinfo/",A4), value(ImportXML(CONCATENATE("http://...
0
votes
1answer
14 views

Getting sheet to not update all at once

I'm making a sheet that imports a large amount of data from a site. I want to do this slowly so as not to clog up the site. function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var ...
0
votes
1answer
65 views

How do I make this Google Sheets Script faster

function getCellValue(){ var x=2; var y=2; var targetSheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); var targetSheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('...
1
vote
2answers
40 views

How to automatically scroll to bottom of Google doc when it is opened?

I'm looking for a way to automatically scroll to the bottom of a Google doc every time I open it. Something functionally equivalent to this answer would be great.
0
votes
1answer
54 views

Equivalent of IMPORTRANGE with script in Google Sheets [closed]

Since the Importrange() is not working as expected, do you know how I could express e.g the following function with a script? ={IMPORTRANGE("SheetKey","SheetName1!A:J") ; IMPORTRANGE("SheetKey","...
0
votes
0answers
11 views

Changed default cell in Sheets to today's date - How do I default it so it displays this cell at startup?

I used this script to default the cell to today's date - How to make Google Sheets jump to today's row when opened? function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet ...
0
votes
1answer
24 views

Multi-var definition statement limit in Apps Script?

My personal javascript variable definition style is a single var statement followed by one or more definitions. It's an unimportant choice, but one that some find benefits in. Carrying this over to ...
0
votes
3answers
31 views

IF and THEN statements on spreadsheets using color

I am creating spreadsheets with multiple tabs for children to work through. To show they have completed each tab, when they have finished the work correctly I am using the following statements: =IF(...
0
votes
1answer
97 views

“missing ; before statement” error

I'm trying to get the number which is basically 116 into a cell on Google sheets with the following code, however I get the missing ; before statement error: var copper = UrlFetchApp.fetch("https://...
2
votes
3answers
81 views

Is it possible to have Named Constants in Google Sheets

Excel has a notion of defined names which can be used to define constants/literals. Does GSheets allow this? You can see this in action in the following Excel screenshot: . Cell B2 has the formula ...
0
votes
1answer
10 views

How to replace reference cell and have it affect the main cell if it referencing?

I asked a similar question on the Google Support Forum. I was wondering how I could reference a cell with any sort of value in it to another sheet (inside the same Google Sheets document) and then ...
0
votes
0answers
45 views

Google script to determine if value is date for email sending

Dates in Google script are driving me CRAZY! I have looked all over the internet for something that will allow me to use dates in my google sheet cells from entries that may be dates or text (For ...
0
votes
0answers
13 views

Copy data generated by QUERY

I want to copy the values generated by a complex scripted query to another sheet, for users to work on without deleting the original query. The query generates a variable range of data, depending on ...
0
votes
0answers
27 views

How do I circumvent protected sheets to expand or collapse groups and rows in Google Sheets?

A budget I am working on has each month on the same sheet and I would like to control which months are visible when comparing expenditure and income. In order to do so, I grouped the rows and columns ...
0
votes
1answer
14 views

Description based on each checkbox “checked” to populate into a summary on the 2nd tab (sheet)

I am building a spreadsheet that has to tab (sheets), the 1st I have an "assessment checklist" with about 250-30 checkboxes based on what is found. Example (Checkbox is in "A" column and description ...
0
votes
1answer
52 views

How to navigate to a certain sheet in Google Sheets?

In my previous related question, I asked how to navigate to a certain column, however, now I also want to navigate to a certain sheet. So I need a link from a cell, and by clicking it, I want to jump ...
0
votes
0answers
16 views

PLease check my delete Function

I modified some google apps script but cant get right the delete (Hapus) function...please see the code below var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/...
0
votes
1answer
100 views

Google Spreadsheets timestamp

I'm trying to have a timestamp automatically appear in column L (12) in a Google Spreadsheet if a tick box is ticked on the same row (in column A). The app script code that I modified from this source ...
0
votes
0answers
46 views

how can i find and append a cell with script editor in google sheets

I have a script that will find the value in a lookup from column b and column a what I am trying to do is find the row and append the data in column n of that row is there a way of doing this, the ...
-1
votes
1answer
121 views

How to run multiple OnEdit scripts in the same sheet?

I am using an auto-timestamp script in Google Sheets. Where when you input something in a particular cell, the script will automatically add a timestamp in a particular cell. But the problem is that I ...
0
votes
0answers
27 views

One script for multiple tabs in one Google sheet

Can you please help me with this script? I've tried to edit it to apply to multiple tabs the same as the original question. But I think something's amiss. :( Here's the original code and I want this ...
0
votes
1answer
31 views

Reference all tabs with a specific word in COUNTIF statement

Basically, I am trying to make a master tab that adds up all of the data from all the other tabs. These tabs are weekly breakdown numbers for ad statistic numbers, so there is a new tab added every ...
1
vote
0answers
40 views

How can I prevent editors from accessing the script editor in Google Sheets?

I have an auto sort script on a Sheets document that I will be giving multiple people editing permission to, to add to the file. I have created a trigger so the script also runs when other users edit ...
0
votes
0answers
20 views

How to select activated sheets cells in Google script, rather than sheet by name

I need my script to talk to the "current sheet" rather than sheet by name. Code as is, selects cells from sheet 2019 but I want the script to copy the values from the current sheet, and paste into ...
0
votes
0answers
20 views

How to use Google Apps Script to skip slides in a Google Slides presentation?

I want to use "metadata" in the speaker notes for a slide to determine whether to skip the slide. The API provides a way to get the notes, which I can then read; does a method exist to apply the "...
0
votes
0answers
20 views

Trigger script in Google sheets with Zapier action

Is it possible to set an action in Zapier (like webhook) to trigger a Google sheets script? What's the right way to set this up? This has been driving me crazy so I hope there is someone out there ...
0
votes
1answer
39 views

Using a script someone else made in Google Docs

I was looking for a script to add to a Google Doc that would automatically insert the current date into the document. I found the script on this page. The last step is to authorize the script and ...
0
votes
1answer
230 views

How can I attach a file to an email using Google script?

I'm using Google Script to send emails, but I'm not sure how to add attachments (multiple attachments). I am also not sure where to add the code for it either. The file is on Google Drive and I need ...
0
votes
0answers
13 views

Sort sheet1 according changes in sheet2

I'd like to sort range in sheetN2 if data in sheetN1 are changed, but the script doesn't work. Could anyone tell me where the problem is? function onEdit(event){ var ss = event....
-1
votes
1answer
1k views

Delete multiple sheet tabs in Google Sheets

In a Google Sheets spreadsheet (Excel terminology = workbook) is it possible to delete multiple tabs (Excel terminology = worksheet) in one go without having to right click each one and delete it.
0
votes
1answer
31 views

What is the updated method of “CalendarApp.openByName”

We use a that gather the registration from Google Form and add them in our Agenda, our script uses the method "CalendarApp.openByName" to add the name automatically inside a Cell. It says the method ...
0
votes
2answers
41 views

Remember each user's last cell cursor location?

Is it possible to have Google Sheets remember each user's either last cursor location or last edited cell or preferably the value of the cell in Row2 that matches the current date? (In Row1 I have the ...