w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
How can I run a google script automatically on multiple sheets in the same spreadsheet
There is no active sheet when your function runs as a trigger. The active sheet is applicable only when a user has the spreadsheet open. Modify your script to use getSheetByName and everything should work fine. If you want this to work on multiple sheets, just call this function from another function.. function shellFunction(){ var sheets = ['sheet1','sheet2',etc]; for (var s in sheets){ toTrigger(sheets[s]); } } And change your toTrigger function to receive the sheet name as argument. function toTrigger(sheetName){ var sh = SpreadsheetApp.openById(ID OF SPREADSHEET).getSheetByName(sheetName); /* Your regular code */ }

Categories : Google Apps Script

Google Spreadsheet Script not Updating Dynamically
I tried adding the GoogleClock() parameter, and it seemed to work fine. For example, in D2: =COUNTIF(getColor("Neshig";GoogleClock()),"#00FF00") This made it update on the minute.

Categories : Google Apps Script

Google Spreadsheet script to color rows containing a certain word and automatically move the row to another sheet
If you consider your question in two parts, you'll find that there are already examples here that address them. For your solution, you could adapt the code from those answers. Previous questions about setting the color of a row according to content: Format row color based on cell value google spreadsheet set row background color based on note Previous question about copying rows containing specific colors to specific spreadsheets: Google Spreadsheets: Find a conditionally formatted colour and copy row Search results: StackOverflow supports rich searching within its database - if you do any search, the result page will include an Advanced Search Tips link, that shows you the operators available. You can learn much more on Meta, if you're so inclined. Here are a couple of the

Categories : Javascript

Google Script - Sending email from spreadsheet - changing the font size
As of right now, you are sending the message as as plain text. If you want to send it with HTML design you will need to use the options tag. See here: https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object)

Categories : Google Apps Script

Google Script: How to Know when somone Login or Logout from Google Spreadsheet and alert me by Email
"Open" event + menu There is an open event https://developers.google.com/apps-script/understanding_events and you could add a menu that people can use to send an automated email before they logout. Re: Srik's comment. A feature request is worth a try, but probably a long shot.

Categories : Google Apps Script

Google Spreadsheet Script invoked too many times per second for this Google user account
Looking at the error you are getting, which says "Script invoked too many times per second for this Google user account", I don't think adding a sleep will help. It is because the number of times your script is called is the point of contention. Try reducing the number of calls to your script in the spreadsheet (basically reduce the number of cells with formulas invoking the script). If your script has cyclic calls, try removing / reducing them. You might get a better answer if you can provide some more information on how many cells call your script and perhaps some relevant code from your script itself.

Categories : Google Apps Script

Google Script: Issue deleting Specific Sheet in google spreadsheet
Try this version I use without issue function DeleteSheets(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ['sheet1','sheet2','sheet3']; var numberOfSheets = ss.getSheets().length; for(var s = numberOfSheets-1; s>0 ; s--){ // in my case I never delete the first sheet SpreadsheetApp.setActiveSheet(ss.getSheets()[s]); var shName = SpreadsheetApp.getActiveSheet().getName(); if(sheets.indexOf(shName)>-1){ var delSheet = ss.deleteActiveSheet(); Utilities.sleep(500); } } SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);// send me back to first sheet } You could of course use the array of names as an argument for the function or - that's what I do in some cases - give the names of the sheet I need to keep, in this case the if c

Categories : Google Apps Script

How can I do a copy of my spreadsheet into specifc folder in google drive using Google Script
Use DriveApp ... get the file using getFileById(id) then use makeCopy(name) then Use DocsList ... get the file using getFileById(id) then use addToFolder(folder) I don't think there is an addtofolder in DriveApp and the file objects in DocsList and DriveApp are not the same

Categories : Google Apps Script

Replicate with Google Script the behaviour of "File" > "Download as..." in Google Spreadsheet
The only way I achieved download of a file from the spreadsheet is by writing completely separate script which accessed my original spreadsheet by document id, generated a file and downloaded it like this: function doGet(){ var outputDocument = DocumentApp.create('Some csv'); var content = getCsv(); var textContent = ContentService.createTextOutput(content); textContent.setMimeType(ContentService.MimeType.CSV); textContent.downloadAsFile("4NocniMaraton.csv"); return textContent; } Then I had to publish the App and get a link for it. That link I placed in one of the sheets in the original spreadsheet, which I used as a data download link. Other then this I didn't find a way to work the security issues around.

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

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

Can I pass an array into a Google App Script method from a Google Spreadsheet?
One option is to pass a string of text and then transform it into an array inside the function myFunc: function myFunc(value) { var arr = value.split(','); } =myFunc("1,2") or function myFunc(value) { /* remember you have to be careful with this function: eval() */ var arr = eval(value); } =myFunc("[1,2]")

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

How to import specific columns from a Google spreadsheet using Google Script?
Check out the 'importrange' function within google spreadsheets (https://support.google.com/drive/table/25273?hl=en) https://docs.google.com/spreadsheet/ccc?key=0AtksdC_OJH9LdDh0eHpaamVBMU1sYUdhMHEwRWw2LXc#gid=0 This is an example of a public sheet pulling in private data.

Categories : Google Apps Script

Copying google spreadsheet columns from one spreadsheet to another with a script
Since you are copying the data into the other spreadsheet you have to specify the other spreadsheet's range object. So, instead of your last line: var target_range = target_sheet.getRange("A1:B2"); source_range.copyTo(target_range);

Categories : Google Apps Script

How do you use modify the color definitions for a Google Calendar to allow custom colors for events?
Added detail from bounty: I have the same question. I would like to be able to set a custom color for events that I post to the calendar, but don't want to be limited by the 11 choices. I see in the docs they have added an additional color setting method (https://developers.google.com/google-apps/calendar/v3/whats-new#custom_colors_f‌​or_calendars). But I'm unclear on the concept of how to go about it. This part of the documentation demonstrates these properties directly: https://developers.google.com/google-apps/calendar/v3/reference/calendarList/insert Which will let you add and modify your personal calendar as a demo (scroll to the bottom and try out the API Explorer). To do what you are asking on here, set colorRgbFormat=true then add the backgroundColor or foregroun

Categories : Api

Updating a published Google spreadsheet with web app?
gspread link is a great Python package to manipulate Google spreadsheets. The documentation is comprehensive and the package is easy to implement. It allows you to address fields via direct cell name or via the cell coordinates. # With name val = worksheet.acell('B1').value # With coords val = worksheet.cell(1, 2).value

Categories : Python

Edit Google spreadsheet automatically from command line
Finally I found how to do it looking to Python code of googlecl. What you need is to build a script that do the following: #!/bin/bash -l #$ -S /bin/bash #S -N $1 export GOOGLE_DOC=$1 #!Then do whahever you need to do with the google file This script is just taking the value of the first argument (which will be the path to the google doc that you want to edit by doing whatever with it). Then all what you need is to call to googlecl like this: google docs edit --title GOOGLE_DOC_TITLE --format FORMAT --editor SCRIPT_WE_JUST_BUILD GoogleCl is creating a temporary file in your system and is passing to the editor(our script) the path to that temporary file. So what we are getting is the path to the temporary file. Whenever yoy finish googlecl will look if there is any change in that fil

Categories : IOS

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

Changing colors on Google Maps
You have to add a styles object to your map options: var blue = [ { featureType: "all", stylers: [ { saturation: 60 }, { lightness: 20 }, { hue: '#0000BB'} ] } ]; The hue will change the color. You have to play around with those parameters until you find the desired blue color. You can apply the colors to the map options: var mapOptions = { styles: blue } And assign them to your map: var map = new google.maps.Map(document.getElementById("map-" + this.spanId)), mapOptions); More info to be found on https://developers.google.com/maps/documentation/javascript/styling#stylers

Categories : Google Maps

Passing in a Parameter in Google Spreadsheet Script from Script Assignment
You could do a couple of things - Ask the user for the input using Browser.inputBox Read cell's value from the script itself (if the cell reference is always constant) using an example like SpreadsheetApp.getActiveSheet().getRange("A1")

Categories : Google Apps Script

How to import multiple csv files (of identical format) and create charts automatically in a Google Spreadsheet?
For an example. I take a FileIterator, a Spreadsheet and put them all to the spreadsheet by sheet. Code.gs function main() { var files = DriveApp.getFolderById(FOLDER_ID).getFilesByType('text/csv'); var csver = new CSVer(); csver.setSpreadsheet(SpreadsheetApp.openById(SPREADSHETT_ID)); csver.importFiles(files, newChart); } function newChart(sheet) { var chartBuilder = sheet.newChart(); chartBuilder.setChartType(Charts.ChartType.TABLE).addRange(sheet.getDataRange()).setPosition(1, 3, 1, 1); sheet.insertChart(chartBuilder.build()); } CSVer.gs var CSVer = function() { var spreadsheet_ = null; this.setSpreadsheet = function(spreadsheet) { spreadsheet_ = spreadsheet; return this; } this.importFile = function(file, funct) { try { var csv = file.ge

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

Amcharts in Google script (via Spreadsheet)
If I understood your question right, you need to use the HTML Service to display that code. Basically, your Apps Script project will have two files, one Code.gs and an html file. Then, use the following in Code.gs, assuming the file with the HTML is called "index.html". function doGet() { return HtmlService.createHtmlOutputFromFile('index').setSandboxMode(HtmlService.SandboxMode.NATIVE); } Good luck using AmCharts, though - I've never tried that one. However, every other charts library I've tried has failed in HTML Service except for Piety. The Caja sanitizer prevents a lot of stuff from running.

Categories : Javascript

copy and paste with google spreadsheet script
This version will find the first empty row in the destination sheet, and copy the source data so it starts there. function moveValuesOnly () { var ss = SpreadsheetApp.getActiveSpreadsheet (); var source = ss.getRange ("Sheet1!F1:H3"); var destSheet = ss.getSheetByName("Feuil2"); // Déterminer l'emplacement de la première ligne vide. var destRange = destSheet.getRange(destSheet.getLastRow()+1,1); source.copyTo (destRange, {contentsOnly: true}); source.clear (); }

Categories : Google Apps Script

Simple script for google spreadsheet needed
The trick here is keeping track of what spreadsheet is "active" - since you want to duplicate the first sheet, you need to make sure the first sheet is "active". (You can't just assume that it is.) Once you've duplicated the first sheet, the new sheet will be "active". That's useful, since some of the operations you want to do will operate only on the "active" sheet. function myFunction() { // Make the first sheet active in the active workbook var ss = SpreadsheetApp.getActiveSpreadsheet(); ss.setActiveSheet(ss.getSheets()[0]); // duplicate the 1st sheet (1st in order) var newSheet = SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet(); // place the duplicate in the first positions ss.moveActiveSheet(1); // rename the duplicate to current date newSheet.setNa

Categories : Google Apps Script

Overwrite Spreadsheet in Google Apps Script
You are probably looking for Sheet.clear(). There are few options for deleting contents and formatting here.

Categories : Google Apps Script

Google Script: How to CopyRows from one sheet to another spreadsheet
I believe your problem is the copyTo() method. That only works when the target is in the same spreadsheet. To fix this you can grab the range that you want from the initial spreadsheet and use getValues() to store them in an object ARRAY. Then open up your final spreadsheet and grab a same size range and use setValues() to dump them. The size of the final range must match the size of the initial range. Here is something that should work: function myFunction() { var source = SpreadsheetApp.openById('xxxxxx'); var sourcesheet = source.getSheetByName('sheet1'); var target = SpreadsheetApp.openById('xxxxx') var targetsheet = target.getSheetByName('sheet1'); var targetrange = targetsheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn()); var rangeVal

Categories : Google Apps Script

Google Script maker to format a spreadsheet
Let's look at the first instance of if(message.indexOf().... It's got some problems: A few lines earlier, message was set to an empty string... so you won't find the "priority" string in it. The if statement is treating the return of indexOf() as a Boolean. However, the return code from .indexOf() is -1 when an item is not found, which is "true-ish". If the string IS found, and is located at the start of the search subject, the return will be 0, which is "false-ish". If it is found at any other location, the value will be `>0', also "true-ish". In the else, there's a typo. The comparison == should be assignment =, or += if you prefer. Looking at the surrounding code, this piece looks like it was left-over from a previous version, and can be deleted. Now look at the second instance. T

Categories : Javascript

Running a google script from within a spreadsheet, but as a different user?
You cannot do it by controlling access to the spreadsheet. The best solution for you is to have your accountant NOT opening the spreadsheet at all ! You can embed a HTML form in the email that is sent to the accountant from where he/she can approve/reject the request. To see how you can build such a system, see this video from I/O 2011 http://www.youtube.com/watch?v=96URE_-aj-8 It demonstrates a use case very similar to yours.

Categories : Google Apps Script

Undefined error on google spreadsheet script
I can not reproduce the problem. The code in question works without problem, as you indicate. Maybe you have to do some validations such as, for example, in sheet.deleteColumn(flagColumn - 6); What if flagColumn is less than 6? I modified a bit the way to get the column that is needed. /* CODE FOR DEMONSTRATION PURPOSES */ function forecastSettimanale() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var searchElement = 'TOT'; var firstRow = 1, firstColumn = 1, rows = 1; var lastColumn = sheet.getLastColumn(); var range = sheet.getRange(firstRow, firstColumn, rows, lastColumn).getValues(); var flagColumn = range[0].lastIndexOf(searchElement); if (flagColumn > -1) { ++flagColumn; sheet.insertColumnsBefore(flagColumn, 1); sh

Categories : Google Apps Script

Is it possible to run the script when the sheet is added to google spreadsheet?
One way I've managed to do this is to define a trigger on the On Change event of the spreadsheet and call the onChange function shown below: /* CODE FOR DEMONSTRATION PURPOSES */ function onChange(e) { var _changeType = e.changeType, _ss = e.source, _newss; Logger.log(_changeType); if(_changeType === 'INSERT_GRID') { _newss = _ss.setActiveSheet(_ss.getSheets()[_ss.getActiveSheet().getIndex()]); Logger.clear(); Logger.log(_newss.getName()); } }

Categories : Google Apps Script

How to edit Charts in Google Spreadsheet with Google SpreadSheet API in VB
Google have Script inside the Spreadsheets that can do that for you. https://developers.google.com/apps-script/reference/charts/

Categories : Vb.Net

Is it possible to include Google App Script (associated with spreadsheet) in JSP or call .gs function in a JSP
You can not directly call the functions of the *gs.file You can send and receive data through ContentService

Categories : Jsp

Writing variables to the next available spreadsheet row in a Google Apps Script
In your Google Apps Script, you will want to look at something called sheet.getLastRow() var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; // This logs the value in the very last cell of this sheet var lastRow = sheet.getLastRow(); var lastColumn = sheet.getLastColumn(); var lastCell = sheet.getRange(lastRow, lastColumn); Logger.log(lastCell.getValue()); Source: https://developers.google.com/apps-script/reference/spreadsheet/sheet#getLastRow()

Categories : Google Apps Script

How to sent an alert when somone open the Spreadsheet using Google Script
There are some features as described in the documentation: Understanding Triggers ... Installable Triggers ... When a Spreadsheet or the Form editor is opened. (Note that this trigger does not activate when a user opens a form to respond, but rather when an editor opens the form to modify it.) Unlike the simple trigger onOpen, the installable trigger can act as the user who installed the trigger. ... The behavior you mention in question is as expected according to the documentation. UPDATE If you have a Simple Trigger (onInstall, onOpen, onEdit) as follows: function onOpen() { Logger.log('getActiveUser(): ' + Session.getActiveUser()); Logger.log('getEffectiveUser(): ' + Session.getEffectiveUser()); } When executed by the owner of the script, the result is as follows: get

Categories : Google Apps Script

Is there a way to create a script for google form to check a spreadsheet first?
You can't enter a code into a Google Form field and have a script function run right after. So that kills being able to check on the validation of another field. Only after the Form has been submitted to go the the responses can you check what the values were. If you stick with a single Google form you could write a script that monitors submissions and sends out an email or letting them know they booked too many people (after the fact). Could also write a script that creates a separate Form for each invite that already has the maximum guests programmed into a drop down list. Then send each invite their own Form url. Another way is to write your own app with one of the 2 UI Services that acts like a form. There you can have interaction with user input and the validation you want.

Categories : Forms

How can copy specifics sheet to another spreadsheet using google script
just look into the docs: copyTo(spreadsheet), from the description: Copies the sheet to another spreadsheet. The destination spreadsheet can be the source. The new spreadsheet will have the name "Copy of [original spreadsheet name]" The method returs the copied sheet so you can rename it: sourceSheet.copyTo(targetSpreadSheet).setName('somename'); I hope, you know how to write a loop in JS ...

Categories : Google Apps Script

Google Spreadsheet Script - the value whcih added up two integer is wrong
The function toFixed can be helpful. console.log((parseFloat("5") + parseFloat("7.56")).toFixed(2)); // 12.56

Categories : Javascript

Google script that find text in my gmail and adds it to a spreadsheet?
You could start with the example in How to use Google App Scripts to retrieve Gmail emails in a customised way? (Author even states "Here's a neat javascript code I wrote to get all emails from my Gmail and put the list of sender name in a google spreadsheet.") From there, you could identify messages from the particular sender you're interested in. (Alternatively, you could use GMail filters to label the email from that sender, then use getUserLabelByName() to grab the threads without requiring a search.) Once you have the threads & messages, you can grab the mail content as a string using getPlainBody(), from which you can retrieve the monetary values. How does one find the currency value in a string?

Categories : Google Apps Script



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