How to Programming with Google-apps-script

var html = HtmlService.createTemplateFromFile('Index') html.id = formId; return html.evaluate() ...

Output API data to a csv file

if-statement,google-apps-script,count,google-drive-sdk

Here is a very basic 2d array to csv converter. Also you can't use the JSAPI library on the server side it will not work. function arrayToCsv(data){ var csv = "" var eol = '\r\n'; for(var i = 0; i<data.length;i++){ csv += data[i].join(); csv += eol; } return csv; }...

google-apps-script count files in folder

I can't find any method to give the count for the number of files, so I guess you'll need to loop through every file, and have a counter: function countFilesInFolder() { var theFolder = DriveApp.getFolderById('your file ID'); var files = theFolder.getFiles(); var cnt = 0; var file; while (files.hasNext()) {...

Calling FormApp within doGet(e) function

google-apps-script,google-form

Under the right conditions for access, the code you're having problems with works. You should review the settings you've used for publishing as a web app. This snippet focuses just on the issue you've brought up, and it works as expected: function doGet(e){ // var form = FormApp.getActiveForm(); // TypeError:...

Apps Script - Web App: Visible Source Code / Library?

web-applications,google-apps-script,source-code

No. Publishing the app, and file sharing are two different things. You can publish an Apps Script app as "Anyone, even anonymous" having access to the web app, but if you don't share the file with anyone, they can not have access to the code. If you shared something like...

Function to check for first blank cell

javascript,loops,google-apps-script,google-spreadsheet

I am not sure of what is the format of your startRow and startCol variables, nor to understand what you are trying to do but I think you should update the variable range in the while loop, because isBlank tests if the whole range is blank or not : https://developers.google.com/apps-script/reference/spreadsheet/range#isBlank%28%29

getJSON from Google Apps Script not working in Safari

google-apps-script,safari,getjson

This might be how Chrome and Safari handles XHR. Apps Script does not support CORS, so you would need to add the 'callback=?' parameter to $.getJson to indicate JSONP. gasUrl + "?username=" + username + "&password=" + password +"&callback=?" Then in your script you would wrap your return in the...

How to real-time monitor the emails?

api,google-apps-script,gmail,quota

The Gmail API recently added push notifications via the google cloud pub/sub service. I wrote an example script on how to do this is Google Apps Script. You can view the code at: https://github.com/Spencer-Easton/Apps-Script-Gmail-Push-Notifications...

How to prevent Google Forms from converting form input to scientific notation format

javascript,google-apps-script,google-docs,google-form

Form responses in Forms (as opposed to Spreadsheets) store responses as Strings. Your trigger function could grab the response from the form to get the string as entered by the respondent. function onFormSubmit(e) { // Get response sheet. First version works only in contained script, // second works even in...

Google Sheets - propagate column date formatting to new rows

google-apps-script,formatting,google-spreadsheet,rows,propagation

Create a function that will run when the form is submitted: Managing Triggers Manually - Google Documenation Code.gs function respondToFormSubmit() { //Format entire columns var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var range = sheet.getRange("M:Y"); range.setNumberFormat('dd.mm'); }; ...

Google Form to Document Merge to Pdf to Mail

when replacing multiple keys in a document you are quite right that the keys must be unique. Personally I bracket all keys with # symbols which works for me e.g. #key1# won't be confused with #key10#

In what format the query parameter in google app script stored?

You forgot to check within the parameters, request.parameter.queryString.

Get column to display month name Google App Script

A lot of typos in your script...(please be more careful when writing code and make use of autocomplete to check spelling : control+SPACE) code goes like this : function myFunction() { var spr = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spr.getSheets()[0]; var column = sheet.getRange("A:A"); column.setNumberFormat("MMMM"); // MMM for abbreviated month, MM...

How to communicate between HtmlService Javascript and Server Google Apps Script

google-apps-script,google-form

A quick test demonstrates that you are receiving your parameters in calculPrice(): function calculPrice(compagnyType, hours) { Logger.log(JSON.stringify(arguments)); ... Log contains: [15-05-29 09:09:21:770 EDT] {"0":"TPE","1":"23"} Since the information is moving from the client side to the server side properly, let's look at the opposite direction. First, validate that your server function...

Modify existing form Values - GetChoices() not working

Item is an interface class that provides a few methods applicable to all form items. "Interface objects are rarely useful on their own; instead, you usually want to call a method like Element.asParagraph() to cast the object back to a precise class." ref Since .getChoices() is a method that belongs...

Google Spreadsheet Addon - unsubmit pending version

You can fill this form, so your issue can be attended by Google: https://support.google.com/chrome_webstore/contact/developer_support/?hl=en

Google Sheets Adds-on open automatically when user opens a sheet or clicks a link in worksheet

google-apps-script,google-spreadsheet,google-docs,add-on,url-shortener

Sure, you can get a UI component for an add-on to open automatically when a document is opened. This is HORRIBLE behavior for an add-on, and it should never be published because it would interfere with other add-ons. But still, it can be done. There are some restrictions, though. The...

Using the UrlShortener API in a custom Spreadsheet function

EDIT: The OP pointed out in the comments that this is a custom function. Custom function run with limited authorization. A complete list of what is available is at: https://developers.google.com/apps-script/guides/sheets/functions#using_apps_script_services Below uses the REST API to get the shortened url. This will work with custom functions. You will just need...

Link in Email to a Google URL not working

html,user-interface,google-apps-script,google-apps,google-form

Try changing the second question mark in the URL to an ampersand. What you have now: var approve = url + '?approval=true'+'?reply='+requestorEmail; var reject = url + '?approval=false'+'?reply='+requestorEmail; Change: +'?reply=' To: +'&reply=' ...

How can I make an RSS feed from Youtube search using Google App Script?

google-apps-script,rss,youtube-api,youtube-data-api,youtube-v3-api

So it would appear that my problem was the version of the published web app. I was not aware that not incrementing the version would cache the app as it was when it was first published under that revision. I noticed that code changes were showing up when I was...

Google spreadsheet script authorisation to BigQuery

google-apps-script,google-spreadsheet,google-bigquery

Unfortunately you can only allow access to scripts running as 'you' if it is running as a web app. The only way to run it as a webapp is if the doGet()/doPost() function is called by the browser. Running the doGet() as a function runs it as a normal script....

Google Script get active row inside Array

google-apps-script,google-spreadsheet,google-calendar

Your code appears to have come from this blog by Tony Hirst. You'll find an updated version of it in Create Google Calendar Events from Spreadsheet but prevent duplicates, which records the event ID to both remember which row has been processed as well as to provide a link back...

Google Apps Script: event.setTime error and time format

Serge has it right - the problem is that you've retrieved a CalendarEventSeries object, not a CalendarEvent. Since the only method in the service that will look for an event by ID is getEventSeriesById(iCalId), you're kinda stuck. One option is to use the Advanced Calendar Service instead: var event =...

Stylesheet for .html document not imported

You're doing this right: Your css file must be an HTML file, as those are the only type supported by HtmlService. If you look at the starter scripts, you'll find that they have Stylesheet.html, with content:  <link...

SpreadsheetApp.getActiveSpreadsheet() is breaking script

google-apps-script,google-form

You must define the method to get data in a .gs file and call it with google.script.run. In some Code.gs file: function getSheetData() { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var data = sheet.getDataRange().getValues(); return data; } And in the html script: function onScrapeClick(){ // Disable Button this.disabled = true; // Get...

Google Apps Script - Sharing outside of domain

You don't have to share the script. The only thing that matters is the way you deploy your webapp : you have to make it execute as you and specify that anyone can use, even anonymous, meaning that the webapp users don't need to log in to use the app....

Execution Success, but no Results?

java,javascript,google-apps-script,scripting,google-apps

Even though you are definitely getting the email that you need from the form, try 'Hard Coding' and email, and see if the MailApp.sendEmail() function works: MailApp.sendEmail('[email protected]", "Approval Request", "what no html?", {htmlBody: html}); After this line of code: var SupervisorsEmail = e.values[2]; //manager e-mail Enter a Logger.log() statement: var...

Delete or Trash specific file in Drive

google-apps-script,youtube-analytics-api,youtube-data-api-v3

I've seen a couple of delete/setTrashed scripts posted here, but they all seem to function for an array of files, and i only want one specific file deleted. Simply put, to delete a single file you delete the first item in the list, what you are calling an array...

Youtube.Playlist.list ReferenceError

All you need to do is change the lower case letter "t" to upper case: var playlistResponse = Youtube.Playlists.list('snippet',{ Change to: YouTube It's case sensitive:...

How to connect PHP GAE(Google App Engine) to Google Docs, Spreadsheets

php,google-app-engine,google-apps-script,google-spreadsheet,google-docs

In PHP, you can make an HTTP Request. In Apps Script, you can create a Stand Alone Apps Script, that does nothing but take a GET or POST request, do whatever you want it to do (E.g. write data to spreadsheet), and then return something. So, you could use an...

Google Apps Script return array values and use them in a javascript function

javascript,arrays,google-apps-script

You need a withSuccessHandler() method chained to your google.script.run: function hello() { google.script.run .withSuccessHandler(injectHTML) .test(); } //This captures the returned string from the server side code function injectHTML(argReturnedArray) { //To Do - code to inject the HTML }; Unfortunately, server side .gs code will only return a string. But there's...

Google Calendar: How to check guest is not busy and available to book through google calendar

google-apps-script,google-api,google-calendar

I did this in google script using freebusy api like this var resource = { timeMin: timeMin, timeMax: timeMax, items: [ { id: calendarId } ] }; var response = Calendar.Freebusy.query(resource); return response.calendars[calendarId]['busy'].length == 0; ...

Fetch HTML Service Values by ID

I believe what Google is doing behind the scenes is extracting the form out as a HTML form and sending it back to the server. HTML forms use the name attribute to store all their name value pairs when sent back to the server. So if this was a get...

Edit a Google Spreadsheet using Google Apps Script

google-apps-script,google-drive-sdk

.getRange and .setValue are the methods you will want to use in whatever you are trying to do. This little function will just take the cell notation as the first argument, and then the value you want to set as the second. function setCellValue(cellName, value) { var ss = SpreadsheetApp.getActiveSpreadsheet();...

Advanced Drive Services in Addons

Yes, you can use advanced services in Add-ons. It only has to be enabled once by you before you publish your script to the Add-on store. All script requests using that advanced service will come out of the quota for the console project associated with your add-on.

Parsing Special characters in google apps script

google-apps-script,text-formatting

Anytime you're looking for an answer about how to do something in Google Apps Script, but it has little-or-nothing to do with Google services, check javascript first. Search javascript diacritics: 68 results You'll most likely find the function you need in Javascript : remove accents/diacritics in strings, which compares the...

How to find text in a string using google script?

javascript,google-apps-script,google-spreadsheet

You need to check if the str is present: if (str) { if (str.indexOf('task') > -1) { // Present } } Alternatively, you can use test and regex: /task/.test("task is completed"); /task/.test(str); /task/: Regex to match the 'task' test: Test the string against regex and return boolean ...

Google Spreadsheet setBackgroundColors() “Deprecated”

javascript,google-apps-script,google-spreadsheet,google-calendar

Use setBackgrounds(). With an s since it's a method that applies multiple background colors to multiple cells

Javascript (Google Scripts) value from a function is not returning

Even if I'm not A JavaScript expert, I'd find it more logical to write it like that (assigning a value directly to the EventId variable) : ... var EventId = createEvent(calendarId,title,startDt,endDt,desc); Logger.log('id after func = '+EventId); sheet.getRange(lr,EventIdHolder,1,1).setValue(EventId); } function createEvent(calendarId,title,startDt,endDt,desc) { var cal = CalendarApp.getCalendarById(calendarId); var start = new Date(startDt);...

Google Spreadsheet - is there any way to export conditional fomatting script

javascript,arrays,google-apps-script,merge

There is no programmatic access to conditional formatting in either Google Apps Script or the Spreadsheet API. All you can do is get the current format (in Apps Script only). Issue 162: Ability to set conditional formatting in SpreadsheetApp That request has been active since 2010. I don't see one...

Merging Multiple Arrays Evenly/Alternating with Javascript and Google AppScript

Use a for loop and the push() method like this : function test(){ var title = ["sometitle1","sometitle2","sometitle3"]; var link = ["somelink1","somelink2","somelink3"]; var date = ["somedate1","somedate2","somedate3"]; var data = ["somedata1","somedata2","somedata3"]; //var all = [title,link,date,data]; var mix = []; for(var n=0;n<title.length;n++){ mix.push(title[n],link[n],date[n],data[n]); } Logger.log(JSON.stringify(mix)); } And also : title[i] + "\n" for...

How to implement rangeProtect() over multiple tabs - google spreadsheet

function,google-apps-script,google-spreadsheet,spreadsheet

Answering my question, The main script discribs the way to find the sheet and the range(s) to protect / unprotect, for different users using the remove/addEditors(mail) function //main script - where to protect - who can edit function Protect_UnProtect( myrange, mysheet, mydescription, startRow, colnumber, numRows, columncount) { var ss =...

Constantly getting an email: Cannot read property “0” from undefined

javascript,google-apps-script,gmail

You need to turn off your trigger. Go to script.google.com. Create a blank script. Once you are in the Apps Script Editor click on "Resources"->"All your triggers" You will see all triggers associated with your account. You can click on the X icon next to the ones you want to...

Google Drive - How to List all Files in a specified folder

php,google-apps-script,google-api,google-drive-sdk,xampp

This is what I came up with, thanks to the help of the above code and some other code I found, put the two together and it does what I need. It may be useful for some one else. Thanks function listFilesInFolder() { var MAX_FILES = 2000; //use a safe...

Google Spreadsheet custom function to add csv in spreadsheet

google-apps-script,google-spreadsheet,google-spreadsheet-api

I guess this way cant work, based on this article, others are facing the same issue. I went with the image workaround that assigns the script to an image and gets called when you press the image. The following error message is displayed: You do not have permission to call...

What is this Google Spreadsheets IMPORTRANGE() Sheet key?

javascript,google-apps-script,google-spreadsheet

That's an absolute cell reference. (The sheet's key is expected to be read from cell O2.) This range syntax is supported by Excel and Google Sheets....

Google Sheets moving rows between separate spreadsheets not working but between sheets is working

I believe I was able to solve this in a simulation environment. First of all, the onEdit method apparently doesn't work when you access other SpreadSheets as it is supposed to be for simple scripts only. So you'll need to create a function with another name and run it as...

Can I add a specific sentence to a cell based off of another cells answer?

google-apps-script,google-sites

Use the debugger, to step through your code one line at a time. In order to do that, you'll need to make some changes to your code. Hard code some values, just for testing: function onEdit(e) { var editedCol = 24; //e.range.getColumn() var editedValue = 99; //e.range.getValue() var editedRow =...

GAS external user autentication

You can invite users who do not have a google account to be a site editor. This is done with the standard site share settings. An email will be sent to the users email you added with a link with an access token. The user will have to click that...

A script to copy one sheet to various spreadsheets in a specific folder in Google Drive

google-apps-script,spreadsheet

I was given this answer by someone within a community in Google+ and it works: function copyToSheets() { var source = SpreadsheetApp.getActiveSpreadsheet(); var sheet = source.getSheets()[1]; var folders = DriveApp.getFoldersByName('ID'); var firstFolder = folders.next(); var folderFiles = firstFolder.getFiles(); var thisFile; while (folderFiles.hasNext()) { thisFile = folderFiles.next(); var currentSS = SpreadsheetApp.openById(thisFile.getId());...

Google Sheets API for clickable images in Google Sheets

google-apps-script,google-spreadsheet,google-spreadsheet-api,google-spreadsheet-addons

Can we also insert this clickable image and link it to app script from our server using Google Spreadsheet API? No. You can't even change the color of a cell. The best you can do is change a formula in a cell. Or Can we call app script from...

Adjust file created date value for date between evaluation using Google Apps Script

This code determines whether today's date is at or past a target date. The target date is 6 days past the file creation date: function olderThan() { var file = DriveApp.getFileById('Your file ID'); var myFileDate = file.getDateCreated(); var creationDate = new Date(myFileDate); var now = new Date(); var dateTo =...

Can I add a Trigger based on change of a particular cell?

excel,google-apps-script,google-drive-sdk,google-spreadsheet,google-apps

This is code taken directly from the documentation: function onEdit(e){ // Set a comment on the edited cell to indicate when it was changed. var range = e.range; range.setNote('Last modified: ' + new Date()); } It already has e.range in it. All you need to do is add and if...

Any way te read an excel file from a Google Sheet?

im giving you an algorithmic-level answer without code because you also have none. with apps script you can import the xls to drive as a new spreadsheet at regular intervals. then, copy its contents to a fixed spreadsheet (replacing previous content). other spreadsheets can read from that fixed sprradaheet with...

Google Spreadsheet (Using named range in script)

javascript,google-apps-script,google-spreadsheet-api,google-form

You are making a simple spelling error, setBackground in this case is without s because you are setting a single color to multiple cells (the argument is "#FFF") So the code is : function resetCheckDirectory() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var tableRange = ss.getRangeByName("NamedRange1"); tableRange.setBackground("#FFF"); }; to summarize : setBackground...

“undefined” when trying to get values from spreadsheet with Google Apps Scripts

Get the correct row, then hard code the column with your URL: var ss = SpreadsheetApp.openById("my-spreadsheet-id") var sheet = ss.getSheets()[0]; var rowForLookup = e.range.getRow(); var columnOfUrl = 24; //Column X var theUrl = sheet.getRange(rowForLookup, columnOfUrl).getValue(); ...

Google Apps Script: Bi-directional sync when rows are deleted

You just have to delete the contents of the sheet before you set the new values. toWorksheet.clear() toRange.setValues(thisData.getValues()); ...

Loading calendar via Scriplets

php,google-apps-script,google-api,google-apps,google-api-php-client

Shouldn't you be using var self = self.replace(/@/gi, "%40"); as %40 = @ using URL escape codes Then the following should work(so make sure you have double quotes around the url): <iframe src="<?= url?>" id="myFrame" style="border: 0" width="800" height="600" frameborder="0" scrolling="no"></iframe> ...

Google Scripts + Google API PHP

I ended up using CURL request directly passing the "Authorization" header with the token_type and the access_token and it worked as expected. I didn't find any way to do this using the Google APIs Client Library for PHP POST Authorization: Bearer [your_auth_token] Content-Length: 0 ...

Form “required” attribute in form input field not causing anything to happen

html5,google-apps-script,html-form,required

The code you are using does not have a "submit" type input (input tag displayed as a submit button). The button in the form you are using is an input tag of the type "button". <input type="button"> The required attribute will not work with an input type of "button". It...

Google Drive App - Method makeCopy (string, string) not found

google-apps-script,google-apps,google-apps-marketplace

the method is makeCopy(string, Folder) so the error you are seeing suggests the TARGET_FOLDER golbal variable you are passing is a String and not a Folder object. You don't give details of what String you are passing in TARGET_FOLDER but if it were a UID, for example, then you would...

Publish a Spreadsheet Add-on in the Chrome Store

Sorry I didn't saw your comment till now Mogsdad! Here is the solution I have found the answer to my problem (I swear I was looking for an answer during the whole day). Anyway, what I had to do was described in Publishing Add-ons for Domain-Wide Installation. This question gave...

Google Apps Script Time Based Trigger Date and Hour

google-apps-script,triggers,clock

The date object can take a time value in its constructor. var d = new Date(year, month, day, hours, minutes, seconds, milliseconds); The trigger will run +- 15 minutes from the specified time....

How to get a button from a Google Apps dialog

javascript,html,ajax,google-apps-script

As you are using jQuery selector you need to enable it using prop $('#dialog-ok-button').prop('disabled',false); //Or true to disable it ...

Google Docs Mail Merge script not working as of last week

javascript,google-apps-script,google-spreadsheet,google-docs

This appears to be Issue 3206, "Sending mail with attachment of Drawing (as PDF) consistently fails". Visit that defect and star it for updates. There doesn't appear to be a new scenario in your script, but if you have any additional info that might help the Googler who is responsible...

Prevent HTML Page Refresh

Since you're technically submitting your form by clicking the submit button, then that creates the page refresh. You need to cancel the submit event with the preventDefault function, which "Cancels the event if it is cancelable, without stopping further propagation of the event." See the docs here: https://developer.mozilla.org/en-US/docs/Web/API/Event/preventDefault So maybe...

DriveApp.getFolderById Permissions

In DriveApp you can get a folder by ID or by name. You are passing the name to the getFolderById() method. Try this below: var myFile = DriveApp.getFilesByName(baseFolder).next(); The getFoldersByName returns an iterator that has the standard hasNext()/next() syntax that iterators carry. The code above saves the first folder returned...

How do I make a Sidebar display values from cells?

google-chrome-extension,google-apps-script

Something like this? This add-on uses the poller idea from How to poll a Google Doc from an add-on to call a server function getRecord(). That function grabs the row of data that is currently selected, and returns it to the showRecord() callback on the client (JavaScript) side, which handles...

Running Apps Script from Chrome extension requires authorization

...no authorization is required to edit it Fine distinction here... no PERMISSION is needed for the shared spreadsheet, however editors must still be AUTHORIZED by the service. For the Sheets UI, being logged in to your Google account is sufficient authorization. Google Apps Script kicks off the authorization cycle...

Script for Gmail label to Sheets

google-apps-script,gmail

Four thoughts for you to explore. Use GmailMessage.getDate() to filter out messages you've already handled. Store the time that the script runs in ScriptProperties and reload it as your threshold next time. Every message has its own unique ID, so recording that along with the body would give you a...

Access dropdown items in Google App Scripts UiApp

google-apps-script,listbox

Sadly not. The best you can do is store the items using the PropertiesService (or CacheService if the list is over 9kb) when the ListBox is created. Then you can walk that list at will later, and use the ListBox setSelectedIndex() method if needed.

How do I select a range of cells in without giving the range a name?

You can use Spreadsheet.getRange(). For example, this should work: var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("..."); var currencyRange = ss.getRange("C29:Z250"); Well, actually I'm using this Sheet.getRange() method instead, but they should be the same in your case. It offers more ways to declare the desired range, such as getRange(row, column, numRows, numColumns), which...

How to pass the “e” parameters to a function called with a trigger?

javascript,google-apps-script,google-calendar

It was a bug. I have noticed that is better to delete the trigger, save the script, and re-create it when changes are made to the function that it calls. This way I never had problems, not doing caused strange behaviors.

printing spreadsheet to pdf then saving file in drive using oauth2

pdf,google-apps-script

You'll find a function that generates and saves PDFs for one or all of your sheets in Convert all sheets to PDF with Google Script. For anyone who hadn't seen the notice posted in the cellar of the Local Planning Office 3 years ago, Google has deprecated OAuth1 & OAuth1a...

Search for value in variable Google Apps Script/JS

Using the Logger a bit more would have given you the solution, or at least shown you where it failed. Try like this : Logger.log('match result = '+res);// when a match is found, res is equal to the matching string if (res!=null) { // otherwise it returns null By the...

Break Google Apps HTMLService Page into multiple .html files

google-apps-script,google-apps

Two basic strategy's would be: Inject all the HTML when your page first loads Inject HTML after the page loads, (like when the user clicks a tab) The first strategy can be accomplished with scriptlets: HTML Service: Templating index.html //include is the name of a function <div id="tabs-2"> <?!= include("HTML_Tab2");...

Apps Script GmailApp archiving not working

google-apps-script,gmail,gmail-api

Okay, so it turns out it was working, but my inbox was just so clogged up that the search I believe only grabbed a maximum number of threads (which must be something like 400-500) at a time, so I didn't really notice a difference. I set it in a loop...

Apps Script - How Do I Remove A Paragraph Of Text?

google-apps-script,google-docs

tl;dr var rangeElement = DocumentApp.openById(copyID).getBody().findText('X1'); if (rangeElement.isPartial()) { var startOffset = rangeElement.getStartOffset(); var endOffset = rangeElement.getEndOffsetInclusive(); rangeElement.getElement().asText().deleteText(startOffset,endOffset); } else { rangeElement.getElement().removeFromParent(); } I was using the following to see if I could find the position of the text I was searching for, but my code returned Range Element. That is...

Unable to select some text from Google Docs with a simple regex

regex,google-apps-script,google-docs

I found out a way to display all of your category_name strings. Main points: Use RegExp exec in a while loop instead od findText To get all occurrences, we need to use g flag with regex To access text in paragraphs, we need getText() Code: var paras = doc.getParagraphs(); var...

Google Sheets Script - changing the contents of a cell based on a given condition

I took a look at your spreadsheet and added some code: function forButton() { //Logger.log('it ran!'); var ss = SpreadsheetApp.getActiveSpreadsheet(); var activeSheet = ss.getActiveSheet(); var masterSheet = ss.getSheetByName('Master'); var inputValues = activeSheet.getRange(2, 1, 1, 3).getValues(); var secondInput = inputValues[0][1]; var allColumnTwoVals = masterSheet.getRange(2, 2, 6, 1).getValues(); allColumnTwoVals = allColumnTwoVals.join().split(","); Logger.log('secondInput:...

How to remove quote mark from array

The code above is dangerous and will break if there is a comma within an element even if it is in quotes which is valid csv. I suggest checking out this library https://code.google.com/p/jquery-csv/. Also, are you wanting to remove quotes or are you wanting to convert string values to numbers?...

How can I exclude a sheet in an array, and sort with a range

google-apps-script,google-spreadsheet,nslookup,dig

There are two questions in here: You need the index of the master sheet. Say it's the first sheet: var allSheets = ss.getSheets(); //get all the sheet var indexOfMaster = 0; //or whichever the index is var sheetsExceptMaster = allSheets.splice(indexOfMaster,1); //splice out 1 item at this index, which would be...

Nslookup or dig in Google App Script

Find a web service that will let you use a GET or POST anonymously to query DNS info using a RESTful API, and you'll be able to use UrlFetchApp.fetch() to access it. For example, StatDNS has a simple API. Here's a custom function that will resolve a Domain Name to...

Google Apps Script (Javascript) - findText returns location relative to inline image, not start of paragraph

javascript,google-apps-script,insertion,inline-images

A paragraph contains several child elements e.g. strings of text or images. You need to get the index of the text element inside the paragraph, not the index of the paragraph inside the document. Try this function placeImage(start, end) { var d = DocumentApp.getActiveDocument() var s = d.getBody(); var logoResult...

Output Google Spreadsheet to XML/RSS/Atom using Google AppScript Content Service

xml,google-apps-script,rss,google-spreadsheet,google-docs

getRange() function only assigns a range. You need to use getValues(). try changing title to this. var title = ss.getSheets()[0].getRange("A1:A").getValues();...

How to get the last entry containing a certain value in Google Spreadsheet

google-apps-script,google-spreadsheet,google-form,formulas

Assuming your data starts in row 2, try: =ArrayFormula(iferror(vlookup(unique(B2:B), sort({B2:B, A2:A}, 2, 0), {1, 2}, 0 ))) Change ranges to suit. Don't forget to include the sheetname if you need that formula to appear on another sheet....

Why is .setUTCFullYear() and .setUTCMonth() return different results

google-apps-script,append,spreadsheet,value

Set the Date before the Month date.setUTCFullYear(input[0]); date.setUTCDate(input[2]); date.setUTCMonth(input[1] - 1); https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/setUTCMonth If a parameter you specify is outside of the expected range, setUTCMonth() attempts to update the date information in the Date object accordingly. For example, if you use 15 for monthValue, the year will be incremented by 1,...

Search Google Spreadsheet for same value and append in next column in GAS

In a Google spreadsheet you can write a range of cells at once. To do so, create a multi dimensional array with one column and for each row another dimension and within each row a dimension for each cell you want to write. Loop through your checkings, set the cell...

Google sheet app script errors after laptop sleep

Henrique provides a good explanation of why custom functions like this don't update in Google Apps - script to summarise data not updating. It comes down to an optimization decision in Google Sheets; a custom function will only be re-evaluated if its parameters have changed. Try to set up an...

Script to check for email subject not in inbox runs without error but not as expected. Not sure what is wrong with syntax.

You've got a few problems - two of them work together to explain your observation. First, you've used assignment (=) where you meant to test for equality (== or ===). The way your comparisons are written, you're assigning subject to msg, and 0 to c, and then testing their "truthiness"....

How to call aspx page from google app script? [closed]