2022 Developer Survey is open! Take survey.

Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

Filter by
Sorted by
Tagged with
1 vote
1 answer
23 views

Choose format for new Date() in Google Apps Script

I have tried almost all suggestions on the internet, yet nothing seems to work. Maybe someone can help a newbie out. I am using this at the end of my script: sheet.appendRow([new Date(), null, text]); ...
user avatar
3 votes
1 answer
119 views

Formula for matrix (dates in descending order)

As a sequel to this question Formula for matrix (dates in ascending order) how would the formula (=IF($B2="Apple",A2-SORT(FILTER(A:A,B:B="Pear", A:A<$A2),1,FALSE),"") ...
user avatar
  • 199
3 votes
2 answers
113 views

Formula for matrix (dates in ascending order)

Firstly, apologies for this question but I couldn't really wrap my head on how to do this (ultimate aim is to put it in app scripts) Let's say I have a table that is updated daily showing the fruit ...
user avatar
  • 199
1 vote
2 answers
26 views

Is setting a trigger for a function to run with a triggered function not allowed?

I would like my function freezeSunday to run at exactly at 1pm on Sunday. Since it does not appear that you can trigger a function to run at a precise time (only at a randomly selected time within a ...
user avatar
0 votes
0 answers
16 views

Insert Dropdown (Cell Range) from Google Sheets into Google Doc

I'm using Google Docs to collect a lot of information about property. Each property has its own Doc file. In this Doc, I have various Dropdown lists. I'm at the point where I need to update and add ...
user avatar
  • 101
2 votes
1 answer
67 views

"Copy to" confusion

In my spread sheet, there are 3 tabs, let's call them A,B and C. Tab A has user input data Tab B picks up that data (get.range and set.formula) and time stamps it (daily interval) with rows appended (...
user avatar
  • 199
1 vote
1 answer
49 views

Appscript: Compare a cell Value pre & post update to populate another cell

Newbie to scripts. Link to the sheet with minimal essential information In the status tab, there would be an inventory of equipment (currently only one (A1). The equipment can be in various states (...
user avatar
  • 199
3 votes
1 answer
51 views

Running onEdit script only in certain sheet

I'm having an issue with one of my scripts created from a macro. I want to update a filter onEdit but only when a certain sheet ("Invoice") is open. Here's what I have now that works across ...
user avatar
  • 31
2 votes
2 answers
72 views

Scheduling a script to run at a specific time rather than a range of time

I set a time-driven trigger to run my script, but the only choices in the pull-down menu for this are 1 hour ranges, 1-2pm, 2-3pm, etc. I chose 1-2pm, and one day the macro ran at 1:38pm and another ...
user avatar
0 votes
0 answers
13 views

How do I forward an email, including the original forwarded message and sender email in Gmail

I am trying to figure out how we can forward the original message and include the recipient email, full chain, so for example: ---------- Forwarded message --------- From: Ram Sam <test@example....
user avatar
0 votes
1 answer
27 views

I need to update the app script to also capture Gmail attachments' name along with other details

First of all, I am not familiar with coding. I have a code that I use to pull certain information from Gmail based on the labels. Now the problem is, the code does not include a line to have the name ...
user avatar
  • 1
-1 votes
1 answer
21 views

Is there a way to use URLFetchApp to behave like importData for CSV rresults

For various reasons (see question Force the refresh of a Google Sheet) I am trying to replace the use of importData in an app script with a custom function that leverages UrlFetchApp.fetch() retrieve ...
user avatar
0 votes
3 answers
33 views

Force the refresh of a Google Sheet

I know that there have been several attempts to answer this type of question that I've found through google searches, but none have worked for me, so I thought I'd ask again. I have a Google Sheet ...
user avatar
0 votes
1 answer
19 views

IMPORTRANGE keeps broken

I have several raw data files which I have linked with importrange to another files. For some reason my files where getting broken since I keep getting importrange internal error. Also, they take too ...
user avatar
  • 1
0 votes
1 answer
16 views

How to make column go reversibly blank with custom validation and conditional format rules

I was able to use scripts editor to make some conditional formatting. Would like the option of covering up the answer column, column B reversibly. Would love some help with a script or how to do this. ...
user avatar
0 votes
1 answer
88 views

Exception: Service Spreadsheets failed while accessing document with id

I've been using this script for the last two years but it stopped working two months ago with the mention : failed while accessing document with id, is there something that changed with Google? ...
user avatar
  • 1
0 votes
2 answers
24 views

How to transpose one tab of data into another tab?

I'm looking for guidance on how to set up my Apps Script file to transpose data from one tab to another. A sample file of my data can be found here. I currently have 10 columns in the rawData tab: [...
user avatar
-1 votes
0 answers
26 views

DATA RANGE TO PDF FOLDER IN DRIVE- " TRIGER SHOWS 'RESPONSE ERROR - 400' OTHERWISE THE CODE IS WORKING FINE MANUALLY" [closed]

function exportRangeToPDf(range) { **strong text** var blob,exportUrl,options,pdfFile,response,sheetTabNameToGet,sheetTabId,ss,ssID,url_base; var currentDate = Date() range = range ? range : &...
user avatar
0 votes
0 answers
16 views

Import coefficients and compare

I have a basic file where I have several parameters (on the left). For each parameter, I have one or more A and B coefficients. In my second file, I have the same thing: parameters (not necessarily ...
user avatar
0 votes
1 answer
28 views

Evaluate ImportJSON() custom function less often

I am using this importJSON script: https://github.com/bradjasper/ImportJSON/blob/master/ImportJSON.gs Screenshot of my sheet: https://puu.sh/IWNt6/5d8a15138d.png I make multiple calls in cells and I ...
user avatar
0 votes
0 answers
27 views

How to create delay when script is running in Apps Script?

Basically, this script looks for changes in a column, then moves the whole row to target tab on next available row... BUT... when it shifts the row up on the original sheet it thinks that same cell ...
user avatar
0 votes
0 answers
27 views

Need help with Sheets Pivot Table Coding

I am trying to code a script to generate a pivot table based on input from drop down menus in Sheets, but I am new to Google Apps Script and having issues understanding the syntax. I am trying to ...
user avatar
0 votes
2 answers
20 views

How can I automate changing the default font in Google Sheets?

It is very tedious to go through all the steps over and over in every Google Sheet to change the default font away from Arial to something that doesn't make my eyes bleed. Is there a way to automate ...
user avatar
0 votes
1 answer
35 views

Move row to very bottom of sheet (Google Sheets Apps Script)

I have a google sheet with a specific number of rows, and I want to use a script/macro to move a certain row to the very bottom. const DESTINATION_INDEX = 4; function myFunction() { const ss = ...
user avatar
  • 253
0 votes
0 answers
18 views

I want to autosort when a change is made on a different sheet, not the active sheet

I have the following JavaScript: function onEdit(event){ var sheet = event.source.getActiveSheet(); var editedCell = sheet.getActiveCell(); var columnToSortBy = 1; var tableRange = "a2:...
user avatar
0 votes
0 answers
36 views

Google app script editor so slow. over 1 minute just to print "hello"

Not sure why this is happening or when it started, but on 4/13/2022, I noticed executions from the editor on Google Apps Script is really slow. It takes over 1 minute to do anything. function ...
user avatar
  • 966
0 votes
0 answers
14 views

"OutputSheet": function on edit: lookup value in "LookUpSheet" and change OutputSheet.value

I have a sheet with a column that contains a dropdown. The options in that dropdown are readible strings (idiot proof) But for further processing, these strings need to be converted to numbers. So on ...
user avatar
-2 votes
1 answer
31 views

Help on making Google Sheets script quicker and more efficient

I have the following script, and I would like to know if there is anyway to make it more efficient? It works great, but takes a little time to do what it needs to do. Mainly is there a way to condense ...
user avatar
0 votes
1 answer
53 views

getRange() Error Exception: The starting row of the range is too small

I have this little script that's giving me some trouble. I get the following error: Error Exception: The starting row of the range is too small appendToRecords @ Code.gs:10 What I'm trying to ...
user avatar
  • 3
2 votes
1 answer
32 views

Google Sheets script add formula into cell but cell value doesn't update

I have a script that when I run it, should add a formula info cells, if the cells are empty, the script works without any problems but if I try to run it again to update the values of the formula, the ...
user avatar
0 votes
2 answers
29 views

How can I sort multiple ranges by one reference cell that is outside the second range?

I have a roster that has (on sheet 1 ('Roster')): Name, 1st Week of Attendance, 2nd Week of Attendance, and then other non-relevant data. On the second sheet ('Contact Info' it has Name, a cell that ...
user avatar
0 votes
0 answers
17 views

Not being a domain admin, how do I resolve an email to a full name or a given name in a Gmail-hosted corp email?

I am writing a small script in a Google Sheet to automate sending tasks to a large number of users (~low 100s). Each message is personalized with the details of the request for each recipient, and I ...
user avatar
0 votes
0 answers
14 views

How to make a script through matching data and paste values to specified fields?

First I would like to point out the fact that I'm nowhere near to the level of a professional programmer. I'm only in the phase of learning and doing it as a hobby. I'm trying to create a script in ...
user avatar
0 votes
0 answers
13 views

Number of lines in Google Slides

I've worked for several projects with google slides in appscripts and slides API as well. However recently I've needed to know the number of lines present in a google slide.(There are no line breaks ...
user avatar
  • 1
0 votes
2 answers
48 views

Converting Google Sheet to JSON format while rows are vertical

I am trying to convert the google sheets into JSON format using Google App Scirpt. It worked but I am not getting the right format and I am little struck. My sheet looks something like this in the ...
user avatar
3 votes
1 answer
97 views

Regex for JSON parsing in Google Spreadsheet Cell

I wanted to use the formula from this topic to extract "count" value in this JSON from a Google Sheet cell. [{"year":2022,"month":2,"count":720},{"year&...
user avatar
0 votes
0 answers
17 views

Running a script on select tabs in Google Sheets [duplicate]

I am not a coder, but I can normally do enough trial and error to get my script to function in the way I want my google sheet to work. The goal of my script: I want cell A to time stamp anytime that ...
user avatar
0 votes
1 answer
42 views

Problem in adding formula in script for updating daily

Newbie to sheets and scripts! I have seen this question,and am partially successful to the extent of creating a new row with date being updated using this script var ss = SpreadsheetApp.openById("...
user avatar
  • 199
0 votes
1 answer
50 views

How to create static/immutable autoincrement column

I am aware of the manual way (add 1,2 in the first 2 rows, mark them, drag the square on the mark-outline down to where u need the numbers) and a number of ways to create the numbers with formula's, ...
user avatar
  • 1
0 votes
1 answer
28 views

How to use a range function with VLOOKUP google sheets

I created this function to get the range from sheets dynamically function GetSheetRange(j) { return SpreadsheetApp.getActiveSpreadsheet().getSheets()[j].getRange(3,1,1000,7).getValues() } I wanted to ...
user avatar
0 votes
1 answer
30 views

Way to keep spaces in Google App Script alert?

SpreadsheetApp.getUi().alert('foo bar') When I use this piece of code, it appears as 'foo bar', removing the additional spaces in between. is there a way coerce Google App Script to do otherwise?...
user avatar
  • 365
0 votes
1 answer
19 views

How to automatically insert datetime across multiple Google Sheet tabs

I have 6 tab Google Spreadsheet that is actively being updated with data into each individual tab. What I am looking for is to enter date-time stamp after each and every entry across all tabs. I have ...
user avatar
0 votes
0 answers
40 views

How to see comment count in Google Docs

I used to be able to see the number of comments in Google Docs as a small blue oval with the number in it, on top of the comments button, but now it's gone. How do I make it come back?
user avatar
0 votes
0 answers
31 views

Scraping Directory contents from Google

Does anyone know of a good way to scrape the information from the Directory in Google Contacts? I saw this How to download all directory contacts?. I tried to run it by following the steps layed out, ...
user avatar
0 votes
0 answers
16 views

Changing Future Monthly Sales Goals Based On Previous Monthly Sales

What I have is a spreadsheet which needs to calculate future monthly required sales goals, based on an annual sales goal, and previous monthly sales. EXAMPLE: My annual sales goal is $1,200,000. This ...
user avatar
1 vote
1 answer
98 views

Version control for Google Apps Script project?

I am using Apps Script to add custom functionality to my Google Sheets: Trouble is, I would like to keep track of changes made to this code base over time. Git is the obvious solution, but I couldn't ...
user avatar
0 votes
0 answers
18 views

The onOpen(e) function stops execution halfway through [duplicate]

I have some code to convert data from a survey to a format that is easier to analyze and put it into a separate spreadsheet. Mind you as of now I am only dealing with 12 submissions. The code for ...
user avatar
0 votes
0 answers
74 views

Unable to correct Margins when converting Sheet to PDF and sending as email via app script

Attaching the snippet of my code. I have an input spreadsheet where the end-user enters in an email address. Then, the code will open my desired spreadsheet (Template1), converts it to a PDF and ...
user avatar
0 votes
1 answer
34 views

I am trying to find a custom function to find the min value of coloured cells ONLY for a range where some cells have a specific color

I am trying to find a custom function to find the min value of coloured cells ONLY for a range where some cells have a specific color. The best I could find is to count the cells with a certain color. ...
user avatar
  • 1
0 votes
1 answer
95 views

App Script dynamic data validation based on list

I have a list of user and data validation mapping in column A and B User Data Validation Customer Jim Customer Pam Customer Tim Employee Beth Employee ...
user avatar
  • 1

1
2 3 4 5
34