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 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

Search for string in all sheets except first sheet and write corresponding sheet names to first sheet of excel file using macros
Lets say we are searching for "happiness" on each sheet except the first sheet. Give this a try: Sub FindingHappiness() Dim N As Long, M As Long Dim s As String, r As Range s = "happiness" N = 10 For M = 2 To Sheets.Count Sheets(M).Activate For Each r In ActiveSheet.UsedRange If InStr(r.Value, s) > 1 Then Sheets(1).Cells(N, 2).Value = ActiveSheet.Name N = N + 1 Exit For End If Next Next End Sub

Categories : Excel

Google Sheets : format a cell differently based on user viewing the sheet?
According to Google Apps Script: getActiveUser() Gets information about the current user. Returns a User object describing the current user. In limited-privilege executions (such as in response to onOpen or onEdit events), we only return the identity of the active user if both the user and the script owner are part of the same domain. This is to protect the privacy of consumer users, who may not want their email address exposed. For more information, see the active user entry in the Glossary. So if you have someone using the spreadsheet from within your domain (for business, education), then you could have an onOpen script and attempt a filter/highlight function based on your parameters and script. Cheers!

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

Update data in 1st sheet based on data from another sheet with google apps script via unique identifier
From your comments it seems that you want to add a column on the matching rows... You can do that quite easily at array level by simply assigning the value to the row array element when a match is found. The only thing to care about is that each row (ie each array representing a row) must have exactly the same length otherwise you won't be able to write it back to the sheet properly. One detail you didn't mention is wether the category row is the last one or not and/or if you have a header on your spreadsheet with a column name for "category". in this first code I will assume you have, then you could simply use data[row][6]=category;// because data[row][6] already exist in this array because the header defines a range width of (at least) 7 when you used getDataRange() ...

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 To Download / Export Sheets In Spreadheet Via Google Apps Script
The download can be done. But not the "Write to the hard drive" of the computer. Write issue: You mean write a file to the hard drive of the computer, using Google Apps Script? Sorry, but you will need more than GAS to do this. For security reasons, I doubt this is possible with only GAS, have never seen anything like this in GAS. Google Drive API will let you do a download, just needs OAuth and the URL you gave.

Categories : Google Apps Script

google script - json into google sheet
I hope this simple code might help you. function myFunction() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets(); var sheet = ss.getActiveSheet(); var dataSet = [ {"id":"xyz","name":"xy"}, {"id":"zyx","name":"yx"} ]; var rows = [], data; for (i = 0; i < dataSet.length; i++) { data = dataSet[i]; rows.push([data.id, data.name]); } dataRange = sheet.getRange(1, 1, rows.length, 2); dataRange.setValues(rows); }

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

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 do I create a Google Script to move information from one sheet to another?
I would start by working through the spreadsheet tutorial for reading/writing. It should walk you through creating the script and the code to access your spreadsheets. From there you might figure out what more specific questions you have: https://developers.google.com/apps-script/guides/sheets

Categories : Google Apps Script

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

How to debug a spread sheet custom function in google script?
When you use a custom function as a formula, it can be evaluated and re-evaluated at many times. Therefore, it is not appropriate to fill up the Logging output or the Execution Transcript with this. If you want to debug, you must run (or debug) the script manually from the script editor. Take an example, where you have two custom functions - f1() and f2() And say, in cell A1, you enter the formula =f1() and in A2, you enter =f2(A1). In such a case, both the cells will be re-evaluated. So what should the logger output show ?

Categories : Debugging

Automatically Copy Sheet 1 A1:A2 to Sheet 2 A1:A2 When Sheet1 A1:A2 Changes
As others have said, you need to put the event handler in the sheet to be monitored. Worksheet_Change will respond to changes made by the user. If a cell changes for other reasons, eg a formula calculating, then this event is not called. Worksheet_Calculate will respond the the sheet recalculating. It has no concept of which cells on the sheet changed. To use it in your use case, either copy the cells regardless and accept it will do some unnecassary copies, or track the values of A1:A2 yourself to copy on change Notes on your code: Unqualified references to Range refer to the worksheet your code is in. So does Me. You can reference a sheets CodeName to to refer to a specific sheet regardless of what the user calls it or moves it. Trying to do an Intersect of ranges on different s

Categories : Vba

After running vba on one sheet, it jumps to another sheet automatically
Without seeing your code, my shot in the dark guess is that some code in Workbook1 is invoking the Application.Run command to execute code in workbook2. Or possibly just opening / activating the workbook2 window at sheet2 as Sean has mentioned.

Categories : Excel

Send email to specific address listed on another sheet using Google Apps Script
You've got the names and email addresses you need to search, in two 2-dimensional arrays. Each of those arrays is an array of rows. A simple search would be this: var email = ''; // If we don't find a match, we'll fail the send for (var row=0; row < names.length; row++) { if (names[row][0] == recipient) { email = emails[row][0]; break; // end the search, we're done } } ... MailApp.sendEmail(email,subject,message); There are more elegant ways to do this, I'm sure, but this should work for you.

Categories : Google Apps Script

Copy row on Sheet 1 to all existing sheets
could do a simple way assuming you have the standard 3 sheet set up (Sheet1, Sheet2, Sheet3) Do all of the changes in Sheet 1, Then click Sheet 2, Shift + Click Sheet 3. in Cell A1, type =sheet1!a1 copy it across how ever far you need. Select Sheet 1 (to remove the grouping) Now sheet 2 and 3 will have the same top row. This works for as many sheets as excel supports. So clicking sheet2, then shift click sheet 50 will fill out all sheets in the range Sheet2 - sheet 50

Categories : Excel

Compare 2 sheets and result on sheet 3
If you compare both loops then I would assume that you need Sheets("sheet2") in this second section: Sheets("sheet3").Range("A" & rowx).Value = Range("A1:B" & i).Value

Categories : Excel

Google Scripts in Sheets: Script Experienced an Error (How to get the error!?)
When you have a failure like this, check the Execution Transcript, under the "View" menu item. It typically shows the results of the last execution of the script, and will indicate the last-run line.

Categories : Google Apps Script

How to compare two sheets in different work books and highlight the difference in 2nd sheet?
Aside from some undeclared variables (using Option Explicit will prevent this, and typos in variable names), your code works fine for me with some minor modifications: Option Explicit Sub compare2sheetsex() 'and highlight the diffrence Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet Dim rCount As Long, cCount As Long Set wb1 = Workbooks(InputBox("enter b1")) Set wb2 = Workbooks(InputBox("enter b2")) Set sh1 = wb1.Sheets(InputBox("enter s1")) Set sh2 = wb2.Sheets(InputBox("enter s2")) rCount = sh1.UsedRange.Rows.Count cCount = sh1.UsedRange.Columns.Count Dim r As Long, c As Integer For r = 1 To rCount For c = 1 To cCount If sh1.Cells(r, c) <> sh2.Cells(r, c) Then sh2.Cells(r, c).Inter

Categories : Excel

Google Script: How automatically open a created text document after have create it
There is no way to automatically open a document, neither a spreadsheet without having the user do something to open it (at least click a link). What you could do is to show a popup in your spreadsheet with a link that would open that document. This is quite simple to do from a spreadsheet script, please refer to this post and to the docslist documentation.(use doc ID to access docsList.getUrl() EDIT : following your comment, here is a possible way to automatically hide the popup, I included this in a full demo code. function test(){ var id = "1cZCL7T-enU0yJZnCb0WM0NeqXDHjnnBUyvs98vsyzwU";// test document (shared in view only) var Doc = DocsList.getFileById(id); showURL('Open document named "'+Doc.getName()+'"',Doc.getUrl()); } function showURL(nameToShow,href){ var app = Ui

Categories : Javascript

VBA: Import Specific Variable Named Sheet in Workbook of Sheets to Access Table?
This is a response for the last part of your question! To upload a table in Excel to an Access Table you will need the following: ADODB library (Microsoft ActiveX Data Object 2.x) array operations You will be using the following objects and methods in ADODB: Connection Recordset AddNew Here is a generic example of using ADODB to append new records to a table in Access: (If you have more than one record that you need to add, you will need to write a loop to create different array containing the values and call the AddNew function repeatedly. As far as I know ADODB does not support batch upload of records*correct me if i'm wrong here) Dim objcon As ADODB.Connection Dim objrec As ADODB.Recordset Dim strSQL As String Dim strCon As String 'important strings you'll need strSQL = "SELE

Categories : Excel

Google Docs - Scripts, copy cell from one sheet to another sheet
Please report back if this is not achieving what you require. function updateVerified() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var source = ss.getRange('Calculates!D5:K').getValues(); var respSheet = ss.getSheetByName('Responses'); var respTs = respSheet.getRange('A2:A').getValues(); var respRange = respSheet.getRange('F2:F'); var respValues = respRange.getValues(); var convTs = [], temp; for (var i = 0; i < respTs.length; i++) { convTs[i] = respTs[i][0] ? respTs[i][0].getTime() : ''; } for (var j = 0; j < source.length; j++) { if (!source[j][0]) break; temp = convTs.indexOf(source[j][0].getTime()); if (temp > -1) respValues[temp][0] = source[j][7]; } respRange.setValues(respValues); }

Categories : Google Apps Script

Google Spreadsheet, Checking the name of the sheet with a cell in a sheet
Problem here: if (Cvalues[j][0] == allSheets) { Your allSheets is an array, so the value in Cvalues[j][0] can never be equal to it. What you probably want it to determine if Cvalues[j][0] appears anywhere in allSheets. Here's how you'd do that: if (allSheets.indexOf(Cvalues[j][0]) !== -1) { ...

Categories : Google Apps Script

Configuring DataView from two sheets in the same Google Spreadsheet file for Google Charts DataSource
The Query API can only select data from one sheet at a time, so you will have to make multiple queries and then join all of the data together into a single DataTable when the queries return: function drawChart () { // use "gid" parameter to select the sheet number // or the "sheet" parameter to select the sheet by name var query1 = new google.visualization.Query(spreadsheetUrl + '&gid=0'); // sheet 1 var query2 = new google.visualization.Query(spreadsheetUrl + '&gid=1'); // sheet 2 var query3 = new google.visualization.Query(spreadsheetUrl + '&gid=2'); // sheet 3 // etc... // set up each query // eg: query1.setQuery('select a, b'); var queriesReady = [false, false, false]; // array should have a number of elements equal to the number of qu

Categories : Google Apps Script

Looping the cells and getting the count of a string value in all the sheets and printing the count sequence in another column in respective sheet
Put the following code and add a reference to the "Microsoft Scripting Runtime" (Tools > Reference) so you can use a dictionary structure Sub CountOccurence() ' Reference: Microsoft Scripting Runtime Application.ScreenUpdating = False Set oDict = New Dictionary Dim Shts() As Variant Dim wS As Worksheet Dim r As Integer, rLast As Integer Dim i As Integer Shts = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4") For i = 0 To 3 Step 1 Set wS = Sheets(Shts(i)) rLast = wS.Cells(1, 1).CurrentRegion.Rows.Count For r = 2 To rLast Step 1 If Not (oDict.Exists(wS.Cells(r, 1).Value)) Then oDict.Add wS.Cells(r, 1).Value, 1 Else oDict.Item(wS.Cells(r, 1).Value) = oDict.Item(wS.Cells(r, 1).Value) + 1 End If wS.Cells(r, 2).Value

Categories : Excel

how to remove first 4 rows in each sheets using vb.net
Not sure what SISS is exactly, had a quick look but not really interested it working it out. It looks like you'll just be using Office Interop in VB.net to manipulate the workbook. You've tagged VBA too, and creating a macro to do this, either manually or using the macro recorder would be very simple assuming you'll only ever have 4 sheets and there always named the same. If you did want to use .net instead of VBA, the code used in VBA is not too difficult to convert to work with .net/interop Example of how to record the macro Start the macro recorder. Select Sheet1 Select Rows 1 to 3 Delete the Rows Select Sheet2 ..... Select Sheet3 ..... Select Sheet4 .... Stop the macro recorder.

Categories : Vb.Net

How to remove background from sprite sheets?
If you dont mind using a library, i use lwjgl,and make spritesheets using texturepacker. Texturepacker generates xml files for textures automatically, and places the textures all on one png. Once i do that, i use gimp, and just use the eraser tool until the background is transparent. Then in your program you just have to type glEnable(GL_BLEND); glBlendFunc(GL_SRC_ALPHA, GL_ONE_MINUS_SRC_ALPHA); and the transparency should work automatically, its a bit more of a pain to actually set up accessing the spritesheets and such though

Categories : Java

c# updating my xml sheet automatically after connecting with a webpage
It's easy to get web page you can do that by using WebClient (How to get data from web page?) or by using HttpWebRequest and HttpWebResponse class, but to get specific data from a web page please follow this (Getting a specific data from webpage using only class items) Download and install the HtmlAgilityPack from nuget. Then using xpath pull specific information as you need. then you can use that information to create xml elements as per you desire. I suggest you to use XElement or XDocument.

Categories : C#

Running a macro automatically in one sheet when workbook is opened
You only missed dots twice but important dots. Your second and third With...End With object reference should start with dots in this way: With .Range("B21") '...your code here End With With .Range("B22") '...your code here End with

Categories : Vba

automatically imposing 4 pdf pages into A4 sheet following custom order
as far I understand, you are trying to Impose 4 pages on every portrait A4, that will give to you 4 A6 single pages (two A5 sheets for A4 page) since dividing the A4 area into 4 parts, means to divide every side by two, so we'll have: 29.7 cm /2 = 14.8 cm (approximated) 21 cm divided by two = 10.5 cm is is obvious, then, that in the same A4 portrait area, can find place, 4 A6 portrait single pages, and 2 A5 landscape A5 pages both, like in picture: so, You don't want be able to make a booklet into A6 format, but only to cut portrait A4 into half to produce 2 A5 sheets for A4 page? Are you not interested to cut further these A5 landscape pages to have an A6 booklet to bind? Since, as far I understand, it seems you are looking for a script able to produce a booklet made im

Categories : Pdf

Google Apps Event changeType undefined in Google Sheet
The documentation you refer to does not concern the simple onEdit trigger, it works with the installable onChange trigger which is completely different. You should rename your function to whatever name you want (but not "onEdit") and add an onchange trigger from the script Editor menu /Resources/current project trigger/ Then if you want to know what value is returned in the event info you can use a code like this : function testonChange(e) { Browser.msgBox(Utilities.jsonStringify(e)); } And you'll see exactly how the event is considered.

Categories : Google Apps Script

one-to-many reporting between sheets in Google Spreadsheets
You asked the question about a year ago, but I still feel it might be beneficial for someone else (it was for me). Here is the "best" solution I found for this (credits go to Alexander Ivanov) Please note that the one-to-many relationship is not saved in a normalized way, rather in a serialized, but it does work out of the box, and might be an inspiration for a more complete and thorough solution.

Categories : Javascript

Single Google Form for multiple Sheets
Step 1: Create Form Normal operating procedure - create your form either through a script or using the Forms UI. Capture the ID of the form. For instance, from the URL when in the editor: https://docs.google.com/forms/d/1-AWccGNgdJ7_5Isjer5K816UKNSaUPSlvlkY3dGJ1UQ/edit ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Attach a spreadsheet to capture responses. (We're not going to do anything more with that here.) Step 2: Client Script In the user-accessible spreadsheet, create a container-bound script (so it has access to the Spreadsheet UI). The following script produces a custom menu with a selection that launches a form in a Ui popup. /** * Uses the Forms service to get a handle on an existing form, then retrieve its published URL. * Uses the UrlFetch

Categories : Google Apps Script

Multiple output calculation SUMPRODUCT (Google Sheets)
In the end, I got the right numbers. There are a number of solutions around for populating a column adjacent to the dataset, but I had a lot of trouble getting a single value solution. The way this works is pretty similar to most SUMPRODUCT formulae: first 2 result sets filter in the bookings where part or all the period lies between the search dates third result set returns the avg PPN for all filtered bookings last result set uses a nested IF() to return the number of booking days that fall between the 2 search dates Hope it helps someone else :) =ARRAYFORMULA(SUMPRODUCT(NOT(GT(Bookings_StartDate,ReportExec_EndDate)), NOT(LT(Bookings_EndDate,ReportExec_StartDate)),IF(Bookings_Nights,(Bookings_ValueA/Bookings_Nights),0),(IF(Bookings_EndDate>ReportExec_EndDate,ReportExec_EndDate

Categories : Misc

How to fill out multiple sheets everytime a Google Form is submitted
You should use a variable that is common to all users and that you increment on each form submission. There are a few possible ways to achieve that, why not try script Properties ? it is fairly simple to implement : First initialize it with a statement like this : ScriptProperties.setProperty('rowValue', 1);// this must happen only once, you could insert it in a small function to "initialize" and then in your code you can retrieve it using var rowValue = Number(ScriptProperties.getProperty('rowValue')); increment it using rowValue++ and write it back to the storage ... remember that you can always edit this value directly in the script properties (script editor>file>project properties> project properties) ScriptProperties.setProperty('rowValue', rowValue); This would be perfect i

Categories : Google Apps Script

Excel: Find match in sheet 1 and sheet 2, copy it in sheet 3
If Sheets("Inventory").Cells(Rows.Count,"A").End(xlup).Row <> Sheets("On Hand").Cells(Rows.Count,"A").End(xlup).Row Then For k = 1 to Sheets("Inventory").Cells(Rows.Count,"A").End(xlup).Row Sheets("On Hand").Range("A" & k & ":E" & k)=Sheets("Inventory").Range("A" & k & ":E" & k) Next k End If For i=1 to Sheets("Sales").Cells(Rows.Count,"A").End(xlup).Row For j=1 to Sheets("On Hand").Cells(Rows.Count,"A").End(xlup).Row If Sheets("Sales").Cells(i, "A") = Sheets("On Hand").Cells(j,"A") Then If Sheets("Sales").Cells(i, "B")<>"" then Sheets("On Hand").Cells(j,"B") = Sheets("On Hand").Cells(j,"B") - 1 If Sheets("Sales").Cells(i, "C")<>"" then Sheets("On Hand").Cells(j,"C") = Sheets("On Hand").Cells(j,"C") - 1

Categories : Excel

Remove Custom Nav Bar on Email Sheet
You can try this: [UINavigationBar appearanceWhenContainedIn: [MFMailComposeViewController class], nil] Which means all navigation bars contained in a MFMailComposeViewController class From the docs: UIAppearance This will return the appearance proxy so you can modify it like this: [[UINavigationBar appearanceWhenContainedIn: [MFMailComposeViewController class], nil] setBackgroundImage:myImage]; Hope that helps.

Categories : IOS

Remove Extra Lines in Excel Sheet Using VB and .cmd
You have these lines towards the end of the Sub: oXL.Quit Handler: oXL.Quit The second Quit call fails, generating the error. You need to exit the procedure just before the Handler (which will only be called in the event of an error): oXL.Quit Exit Sub Handler: oXL.Quit

Categories : Excel



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