w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
exceeded maximum execution time in google-apps-script
You should avoid any calls to spreadsheet API in a loop, especially when you have so much data in it. The idea is to play only with arrays and to write the result back once it is finished. The code below does it (based on you code if data in col C sheet1 is the same as data in sheet2 col A then write a string in sheet1 col A ). I hope I made no mistake but I didn't have the opportunity to test my code... it might need some debugging ;-) function compare() { var ss=SpreadsheetApp.getActiveSpreadsheet(); var sheetList1=ss.getSheetByName("Sheet1"); var sheetList2=ss.getSheetByName("Sheet2"); var sheet1Data=sheetList1.getDataRange().getValues();// get the whole sheet in an array var sheet2Data=sheetList2.getRange(1,1,sheetList2.getLastRow(),1).getValues(); var newSheet1Data = []

Categories : Google Apps Script

Reducing Google Apps Script execution time with using an array?
Transposing in 2D array is very simple. The main difference is the way data is indexed : ranges count from 1 and arrays count from 0. So to transpose your code you should get 2 arrays (one for each sheet) and iterate the corresponding cells, change the value depending on your condition and write back the array to the spreadsheet to update it. Here is a rough transpose of your code with a couple of comments to explain : (some variables ought to be renamed for clarity) function copyFunctionDATA() { var defSheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("(DATA)").getDataRange().getValues();// read the whole sheet in a 2D array var defSheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("(DATAdead)").getDataRange().getValues();// read the whole sheet in a 2D arr

Categories : Google Apps Script

Google Apps Script formatDate using user's time zone instead of GMT
You can directly get the spreadsheet Time Zone like this : var addedDate = sheet.getRange(1,1).getValue(); var addedTime = Utilities.formatDate(addedDate, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "hh:mm a"); See doc here but Google Apps Script uses these formats if you want to choose it yourself : http://joda-time.sourceforge.net/timezones.html

Categories : Javascript

Through Google Apps Script get all user information on a spreadsheet of a google apps domain
Some of what you are looking for is best requested via the Admin SDK. You may want to also check out other Google Data APIs in more detail, you could put together some good reporting tools. Many of the APIs can be used in Google Apps Script by using the UrlFetch service. There's an OAuth flow built in that service that is quite nice for OAuth 1 flows, and it's not too bad to create a decent Google OAuth 2 flow with UrlFetch as well. Then you can make authenticated requests to those APIs and handle the responses accordingly with Apps Script.

Categories : Google Apps Script

How to sign out user automatically from Rails app after a certain period of time?
I use a similar method, but I'm using sessions not cookies. I have an 'authenticate' action in my application controller which is triggered by a before_filter for each controller, this action pokes the 'session[:accesses]' to add 1 which in turn updated the 'updated_at' column in the database. I then have a cron job that removes any sessions from the database that haven't been updated in 20 minutes.

Categories : Ruby On Rails

Google Maps automatically convert period (.) into comma (,) from my Intent URI
You can use the following snippet to solve the problem public String getCoordinates(String coordinates){ if(Locale.FRANCE == Locale.getDefault()){ Pattern p = Pattern.compile(",.*?(,)"); Matcher m = p.matcher(coordinates); if (m.find( )) { int index = m.end(); //gets the second comma position String str1 = coordinates.substring(0,index-1); String str2 = coordinates.substring(index,coordinates.length()); NumberFormat nf = NumberFormat.getInstance(Locale.FRANCE); try { str1 = nf.parse(str1).toString(); str2 = nf.parse(str2).toString(); } catch (ParseException e) { e.printStackTrace(); } return str1+","+str2; }

Categories : Android

Google Apps Script Spreadsheets - Assign script to image from a script
This would be a cool feature, but here is a possible workaround, it might even offer more functionality. You can build a UI using Google API. More detailes here. The UI allows you to create side bars/menus/etc. You could even create floating dialog windows. Using AbsolutePanel, you can even choose where the dialogue shows up.

Categories : Google Apps Script

Run a line of script via a variable based on a value inside the worksheet - google apps script
One option may be to use eval(), but remember you have to be careful with this function. ... var doThat = ss.getRange("A1").getValue(); eval(doThat); ...

Categories : Google Apps Script

How to separate script editor from document (google apps script)
Yes, when you create a new document in Drive you can select Script. If you don't see the script option, try going here. You will, however, need to call your document using getByID. There my be other complexities if you are using onEdit type triggers. A lot more information can be found under the Types of Scripts heading at Google Apps Scripts Documentation. You could go advanced and pull your script in behind the Document as a Library and then using the script behind the Doc to make the calls to the library, but that's a bigger discussion.

Categories : Google Apps Script

Google apps script :: Is it possible to get the cursor position or get the selected text from Google Document
Currently there is no such API. However, there is an open issue on the Issue Tracker. You can star this issue to register your interest and be notified to update to it

Categories : Javascript

Text formatting for strings in Google Documents from Google Apps Script
Use the functions describe at the API site here You can manipulate the text setting all styles as much as you want. For example: var file = DocsList.getFileById('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); file.replaceText('Old text string',newTextString); var newTextString = 'foo bar'; var doc = DocumentApp.openById('DOCUMENT_ID_GOES_HERE'); var text = doc.editAsText(); text.setItalic(startOffset, endOffsetInclusive, italic)

Categories : Javascript

how to read, with google apps script, the heading reference in a google docs
Is it possible to read the heading reference at all? Absolutely, at least from the Table of Contents. Those references are in the attributes of the TOC entries. You can see an example, with a script, in this answer.

Categories : Google Apps Script

Need help finding the maximum value in a column in Google Sheets using Google Apps Script
You can use the method setFormula(formula) and use a formula like =MAX(Range). The method getDataRange() might be useful. After you get the range of data you can determine the maximum number of rows getLastRow() and columns getLastColumn().

Categories : Google Apps Script

How to edit the document after uploading it on google drive using Google Apps Script
Currently, when you upload a document/spreadsheet using Apps Script, it is uploaded in the same format i.e. it is not converted to Google Docs format. There is an open issue asking for this feature. You can star the issue to register your interest.

Categories : Google Apps Script

Is it possible to Use Google Apps Script to check condition in Google Drive Forms?
Google Forms does not support client-side validation like that, at this time. Deploying an Apps Script as a web app would allow you to do it, but you have to create all of the formatting and functionality yourself. The recent inclusion of data validation functions in Apps Script in conjunction with Google Forms supporting some scripting access means that we could see something along these lines in the future. Would certainly go a long way toward making Google Forms more useful.

Categories : Google Apps Script

Upload text file to Google Drive with Google Apps Script
To do a file upload, you cannot use a server handler. You must use a FormPanel and a Submit button. See the FileUpload widget's documentation.

Categories : Javascript

Modify Google Spreadsheet via Google Apps Script Values Only
function archivedata(){ //creates my archive spreadsheet var ss1 = SpreadsheetApp.getActiveSpreadsheet(); var data = ss1.getSheetByName("Sheet1").getDataRange().getValues(); var ss2 = SpreadsheetApp.create("New spreadsheet"); ss2.getActiveSheet().getRange(1,1,data.length,data[0].length).setValues(data); ss2.renameActiveSheet("newsheet"); }

Categories : Google Apps Script

Can the google spreadsheet 'query' function be used in google apps script?
No, there is no API for the Query function that allows it to be called from Google Apps Script. (There is no way to call ANY spreadsheet function in this way, in fact.) You can get some similar functionality without writing it all yourself, though. The 2D Arrays Library includes a variety of "filter" functions that let you retrieve matching rows.

Categories : Google Apps Script

How can I get URL parameters passed to a Google Spreadsheet using Google Apps Script?
This is not possible today. As you noted, doGet, doPost are the ones that can grab the variables when deployed as a web app. Please log an enhancement on the issue tracker

Categories : Google Apps Script

Google Apps Script run when SpreadSheet is change by another Script
The onChange() function will not have reference to the UI. For example, if there are multiple people who have the same UI open, which one should be updated ? So, the solution to your problem is for your UI code to poll the spreadsheet regularly and update the UI if the spreadsheet has changed. To do so, you can use the undocumented addTimer() method. See this SO question & answer for a nice example

Categories : Google Apps Script

getting Current script version using Google Apps script
If you mean "Can we programmatically retrieve the script version number?", the answer is no. There is an outstanding request for this, in Issue 1369, so visit the issue tracker and star it to indicate your own interest. Perhaps you mean "Can we programmatically retrieve the most recent source code?" You can Import and Export Stand-alone script files through the Drive SDK. In Apps Script, this can be accomplished using UrlFetch Services. There is no way at this time to retrieve the source of container-bound scripts, except through the Script Editor. (See Limitations.)

Categories : Google Apps Script

Count within a time period, a group of a smaller time period
Yup, possible with a GROUP BY and a HAVING, using some UNIX_TIMESTAMP rounding (subtracting stamp % 300 from stamp) to group into blocks of 300 seconds (5 minutes). SELECT COUNT(*), MIN(stamp) FROM mytable WHERE stamp >= NOW() - INTERVAL 30 minute GROUP BY FROM_UNIXTIME( UNIX_TIMESTAMP(stamp) - MOD(UNIX_TIMESTAMP(stamp),300) ) HAVING COUNT(*) >= 4 This assumes that you mean clock blocks rather than rolling blocks, i.e. you want from 00:00:00 to 00:04:59 etc., rather than from 13:23:01 to 13:28:00. This causes some issues with the "30 minutes ago" rule, so you might want to think that one through a bit carefully. EDIT: Well seeing as you insisted on making it a rolling block... let's add some delicious awfully-performing joining. SELECT COUNT(*), rolling.s

Categories : Mysql

How could I get the Google Site's div width with Google Apps Script?
I don't think it is possible. That is because your Apps Script resides within the Google Site and it cannot access any of the DOM outside the Apps Script's UI. You can open an issue in the Issue Tracker

Categories : Google Apps Script

doPost Twice in Google Apps Script?
I think you are just adding the wrong UI element to the app in your doPost. Instead of app.add(app.getElementById('formContent')); use app.add(app.getElementById('mainForm'));

Categories : Google Apps Script

Google Apps Script CoffeeScript
You can't, because it's a JavaScript execution environment, not a CoffeeScript execution environment. Google would have to add support for this themselves. You could write locally in CoffeeScript and put the output code into the Google Apps Script editor?

Categories : Google Apps Script

Google Apps Script in Form
If you are building a form that you will be emailing to the students, then when you set up the form you can have it log all their email addresses for you without you having to write any script at all. Here is a great way to get started writing a new form... go to this page: http://www.google.com/drive/apps.html?usp=ad_search and scroll down to the "Forms" section then click on "Create". This should walk you through everything you will need to set up the form, and the results will be put into a spreadsheet for you, along with the students' email addresses. You can keep the resulting spreadsheet private so that you will be the only one who can view all the responses. I hope that helps.

Categories : Google Apps Script

Is it possible to scale Google Apps Script?
Your GAS script scales up automatically. The only thing that you should be worried about is your code where, if you have locks, thousands of users waiting for a lock will cause delays to the user. Other than that, scaling up shouldn't be a problem. After all, there are possibly millions of scripts being run by different users.

Categories : Google Apps Script

Google Apps Script url "routes"
The doGet() function has access to GET parameters, but not the URL itself, so you can't really do what you're thinking of with Apps Script. What you could do is use Google Apps Script as a backend to a web app running something like AngularJS, which has client-side routing. You have to host the app elsewhere (the Caja sanitiser doesn't support serving AngularJS yet), and you have to use JSONP as your exchange format, as you can't use Apps Script apps for AJAX endpoints. Here is an example from Keisuke Ohashi: Front end (note: hosted on Google Drive) Content service code ScriptDB code

Categories : Google Apps Script

RequireJS with Google Apps Script
I found the answer while reading JavaScript Module Pattern: In-Depth The code inside the module didn't have access to Logger. Passing this as a parameter makes it work. define( function(g) { g.Logger.log("Monkey"); }(this));

Categories : Google Apps Script

Google Site Apps Script
How does the script run and who had access to it. There are 3 (or4) access levels for any web app - only you, anyone in the domain, anyone, anyone including anonymous. Have you set the appropriate access permission for your app.

Categories : Google Apps Script

Browser Pop up in Google Apps Script
A jQuery dialog will suit your needs. It is an overlay to the current window - not a "pop up". The demo code here can be easily adapted to Google Apps Script. Here it is, with much of the extra bits removed: Code.js function doGet() { var template = HtmlService .createTemplateFromFile('ModalForm'); var htmlOutput = template.evaluate() .setSandboxMode(HtmlService.SandboxMode.NATIVE) .setTitle('jQuery UI Dialog - Modal form'); return htmlOutput; } ModalForm.html <!-- Adapted from http://jqueryui.com/dialog/#modal-form --> <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css" /> <script src="http://code.jquery.com/jquery-1.9.1.js"></script> <script src="h

Categories : Javascript

Google Apps Script Web App on my Domain
This isn't currently supported. See (duplicate) Embedding Google Apps Script WebApp in WordPress Page. Visit Issue 852 and star it for updates. (There are also some potential work-arounds described in the issue comments.)

Categories : Google Apps Script

Google Apps Script API rackspace DNS
I found the solution: var Url="https://dns.api.rackspacecloud.com/v1.0/829221/domains/" ; var response1 = UrlFetchApp.fetch(Url, { "headers": { 'X-Auth-Token': Token }})

Categories : Api

PDF prevention to download on Google Apps Script
I have investigated this in the past and it was not possible, I looked again today at both the Drive and DocList API through google script and its not possible still. A cursory look at the DRIVE SDK does not indicate that is possible either. What is the use case you are looking for? Many a time you can wrap the doc in a iframe on a Google site to prevent downloading.

Categories : Pdf

Google Apps Script - send pdf in email
The argument needed for the optional argument attachment is an array (as clearly shown in the documentation). This is to allow for easy handling of multiple attached files. In you case it will be an array of one single element : [file] so your final code would (indeed) be MailApp.sendEmail(userEmail, subject, message, {attachments:[file]});

Categories : Email

Has the Url Shortener in Google Apps Script changed?
Yes. It has. I figured it out the hard way. Just make a small change for it to work function getShortenedUrl(url){ var longUrl = UrlShortener.newUrl(); longUrl.setLongUrl(url); var shortUrl = UrlShortener.Url.insert(longUrl); return shortUrl.getId(); }

Categories : Google Apps Script

How to allow others to access a web app made from a Google Apps Script?
The link you show here is the development link , the one that is described as "the last saved version of your code", this link is not shareable ... the one you should give to people is this one : https://script.google.com/macros/s/AKfycbz1SKJi5HW1r2ZHiYpjedjul-IE4vcB266kdDzDrVCE/exec it is shown in this popup window beside that, the sharing properties of the script file is not relevant, you could keep it private.... only the settings in this window are important to determine who has access to your app and how (meaning run as you or as user accessing the app). You should also keep in mind that users will see the version you have chosen in this same popup window, no matter the changes you could have saved and are using in your .dev version... so think about publishing the right version

Categories : Google Apps Script

Google Apps Script: Logger Not Formatting
Output from a standalone script, where it works just fine (as reported by @Fred as well): [13-06-17 15:45:24:889 EDT] Will this work? [13-06-17 15:45:24:889 EDT] Will 'this' work? [13-06-17 15:45:24:889 EDT] Will [this] work? [13-06-17 15:45:24:889 EDT] Will "this" work? [13-06-17 15:45:24:890 EDT] Will this work? [13-06-17 15:45:24:893 EDT] Will [this] work? [13-06-17 15:45:24:893 EDT] Will this work? [13-06-17 15:45:24:894 EDT] Will 'this' work? [13-06-17 15:45:24:894 EDT] Will [this] work? [13-06-17 15:45:24:894 EDT] Will "this" work? [13-06-17 15:45:24:894 EDT] Will this work? [13-06-17 15:45:24:895 EDT] Will [this] work? [13-06-17 15:45:24:895 EDT] [Mon Jun 17 2013 15:45:24 GMT-0400 (EDT)] is today [13-06-17 15:45:24:901 EDT] [Mon Jun 17 2013 15:45:24 GMT-0400 (EDT)] is today

Categories : Google Apps Script

How to combine Google Apps Script with JavaScript?
You can use a Google Apps Script project (from drive, create script). using the HtmlService class you can create the HTML page with the form and the client Javascript (included Jquery if you need it) and of course can use Google Apps Script (server javascript). You need to make a web apps, adding a doGet function in the Google Apps Script code (.gs). Example: function doGet() { return HtmlService.createTemplateFromFile('index').evaluate(); } You have to save a version of the app and then publishing as web apps (using the menu). Then you have to add an index.html file with the form: menu file, new, "html file" and then include a javascript file. Example: <div> <form action=""> <input type="text" value="" name="name"> ...... var msg= "You can also

Categories : Google Apps Script

UrlFetch put method using Google Apps Script
Credentials are needed to test this, so that's up to you. You probably need to both stringify & encode the payload. You also had incorrect capitalization on contentType, which you could check with UrlFetchApp.getRequest(). function updateProducts_version2a(){ try{ var url ="https://app.ecwid.com/api/v1/xxxx/product?id=xxxxx&secure_auth_key="+products_authkey; var payload = {price:62755}; var payload_json = encodeURIComponent(JSON.stringify(payload)); var options ={method:"put",contentType:"application/json",payload:payload_json,muteHttpExceptions:true}; var result = UrlFetchApp.fetch(url, options); var response = result.getContentText(); var res_code = result.getResponseCode(); var x = 1; }catch(e){ Browser.msgBox(e); } } This next versi

Categories : Javascript



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.