New answers tagged

1

I don't know of a way to select a non-contiguous range via URL parameters. The solution I found is to create a new temporary spreadsheet, copy selected columns there, and export the new sheet into PDF. In the script, the array cols holds the columns to be taken: B-E and G correspond to 2,3,4,5,7. Version 1: print without formatting function printPDF() { ...


1

Short answer The link points to a Q&A that has two answers but non-of them looks to me to be appropiate. I.E. the code of one of answers is incomplete and both of them send emails, so the adoption is too complex to be used as a starting point. Below is a script that is easier to adopt. Instructions Create a test form and set it to send responses to ...


1

Here is a script that does the following: if a user enters 'DONE' in any cell in sheet named 'Sheet1', the row with that cell is copied over to Sheet2, and deleted from Sheet1. To use, just enter in Tools > Script Editor, and change sheet names if needed. The script does not check that 'DONE' is in the last nonempty cell of a row: it can be anywhere. ...


2

I modified the script so that it accepts arrays of column headers: updateColNames lists the columns that you want to keep track of timeStampColNames lists the columns where timestamps should appear, in the same order. There is now a for loop that goes over the tracked columns one by one, comparing them to the edit location, and inserting timestamp when ...


2

The problem is here: all_sheets.push( [ sheets[i].getName() ] ) This should be changed to all_sheets.push( sheets[i].getName() ); The second version inserts a sheet name into array, which is what you want. The first version inserts a one-element array into array, resulting in a double array like [['Sheet1'], ['Sheet2'], ['Sheet3']]. For ...


2

Custom functions are re-run when their parameters change. For example, if you have =myfcn("sheetname", 1, 1, 1, 1) and change some of parameters here, the function will run again. But if you go and change the content of cell sheetname!A1, the function will not re-run, since there is no indication in the spreadsheet that its output depends on sheetname!A1. ...


1

Your draft is pretty good, but there is a design flaw: if you insert the values between B and C, then the column with formulas will become D. So, next time the script will try to get data, it will be looking at a wrong place. Simply put, the source of the data you are recording (i.e., the column with formulas) should stay in the same place. You can put the ...


1

This is what you can do: Go through all the rows and find the first row with valid e-mail address Continue until you find an invalid e-mail address Re-retrieve all the data in the range and change "@" to "#" This way, you can keep adding new rows and the code will still work. And the code shouldn't mess with any formulas you might have at bottom of the ...


2

The event object helps here, specifically its changeType property, which tells the script what kind of change happened. If this change is INSERT_ROW, and the name of sheet matches the one you want, then an email is sent using MailApp. function notify(e) { var sheet = SpreadsheetApp.getActiveSheet(); if (sheet.getName() == 'Tracked Sheet' && ...


2

You can do a lot of things with Google Forms responses with basic programming skills and knowing JavaScript as ome tasks with Google Forms responses could be automated with Google Apps Script with a bounded script to the form. One of the keys for your project is to use Google Apps Script installable triggers, specifically time-driven triggers to be able to ...


2

Yes, you can do all of this with Google Apps Script, though it's a pretty large project that arguably should not be someone's first project with GAS. Here is an overview of methods involved, with pointers to documentation. Select a range of cells in a row: getRange. Create a graph based on that range of cells including key and labels: see Embedded Chart ...


3

Getting and setting datetime values Whenever your script calls .getValue() on a cell formatted as a date, date-time, or duration, it gets a JavaScript Date object. You can then apply various methods listed on that MDN page to this object, including getMonth() for the month. Example: var sheet = SpreadsheetApp.getActiveSheet(); var value = ...


3

Short answer Below two custom functions are presented as examples of how Google Sheets and Google Apps Script could work with dates. Example 1: Get the newest date function myFunction(date1,date2) { return new Date(Math.max(date1,date2)) } Example 2: Get the month of the newest date function myFunction2(date1,date2) { var date = new ...


3

Short answer getUserLoginID is deprecated. Instead use getMail. Explanation From https://developers.google.com/apps-script/reference/base/user getuserLoginID() Deprecated. As of June 24, 2013, replaced by getEmail() getEmail() Gets the user's email address, if available. If security policies do not allow access to the user's email address, ...


4

The issue here is on JavaScript level, not specific to Apps. The method setMonth that you use modifies the date object on which it's called. You don't need any assignment on that line. MDN docs don't even say what setMonth is supposed to return, but apparently it returns the millisecond timestamp corresponding to the new date. In any case, this is what ...



Top 50 recent answers are included