New answers tagged google-apps-script
0
Insert a month column in the first table, and create your report that breaks down the data by month using Data > Pivot table, or a query() formula with a pivot clause by the month column.
-2
please set your image size to standard size. for example 800x600, 640x480, 400x 300, .... and then upload again
1
In Google Sheets, custom functions are recalculated when one of its parameters change. NOTE: They don't allow volatile functions (NOW, TODAY, RANDOM, etc.)
In order to get the current number of sheets on something close to real time, instead of using a custom function you might use a change installable trigger (usually referred as onChange) as it's triggered,...
0
You can modify the getCountryList() function so that it accepts a range argument, and add online function help, like this:
/**
* Guesses the country of a number of addresses listed in rows.
* Each address can consist of several consecutive cells in the same row.
*
* @param {A2:D} addresses The range that contains street addresses.
* @return {String[][]} The ...
0
You want to know how to get index of edited row.
The edited row is rRow. (Refer lines 27 & 28 of the answer)
var response = e.range;
var rRow = response.getRow()
The following answer is based on linking a spreedsheet to the form for output of responses; and copying of responses to second )working) sheet for calendar analysis and addition of relevant ...
2
You are using the RangeList.clearContent() method to remove data. This makes the value in the checkbox cells null instead of false or true.
The query() function only accepts one data type per column. The type of a column is determined by the majority of the values in the beginning of the column. When you clear the data, the data type of column Data_Entry!H:H ...
-1
I cannot comment on Alexis answer without reputation on answer
/2021 VERSION - This is a solution to copy a Google Form responses (answers) to another Google Form.
So I am putting it here. I do not know, if its right.
I tried to run script as given in tutorial and faced following error
1:21:44 AM Notice Execution started
1:21:45 AM Error
TypeError: Cannot ...
0
First, you'll want to prepare the cells in the results range to display the images. In the case of your post example and sample spreadsheet, right-click on the "2" to the left of Row 2, choose "Resize row" from the flyout menu, click the radio button by "Specify row height" and enter 58 for the value.
Once that is done, place ...
0
I am using this code and it works perfectly. Make sure:
Edit form function is turned on
get the right form ID (the one you need to use is the URL of when you are creating the form itself NOT VIEW MODE URL)...I also made this mistake and it took me a while to figure out
var formRes = FormApp.openById('XXXXXXXXX');
var sheetRes = SpreadsheetApp....
1
This error is likely caused by calculations not completing before the script tries to shift the ranges still actively being calculated. This is why the issue may arise intermittently.
For example, if you have a number of formulas that are dependent on cells that are being shifted by the code, they will be triggered to recalculate when the cells they ...
0
You can use a spreadsheet formula that employs an { array expression } to transform data in a form responses sheet into a new sheet that has a row-oriented layout where row data is repeated the way you describe.
See the Array Expression Formula Builder spreadsheet for examples and instructions on how to build a formula to expand the data to multiple rows. ...
0
A single onEdit(e) function can watch multiple columns on multiple sheets and insert timestamps in multiple columns depending on which column was edited and what the new value in the cell is. See the timestampMultipleColumns_ script for an example.
1
There is no simple trigger that runs when a filter is applied, but you can use an installable trigger to detect that. You can test an installable "on change" trigger like this:
function onChange(e) {
console.log(JSON.stringify(e));
}
To make the code run when the spreadsheet is changed, click the clock icon 🕓 in script editor, click +Add ...
1
An onEdit(e) only runs when the user hand edits the spreadsheet, and no events get triggered when a formula result changes.
Your description suggests that you have a formula cell in column Q in every row, and the value in that cell changes when other cells in the row are edited.
To insert a timestamp, you will need to make the onEdit(e) function read the ...
0
You want to execute a trigger when a new row is inserted but you are concerned about how the onChange trigger may confuse an "edit" with "add new row".
The correct Trigger is onChange. This supports nine (9) change types. One of these is EDIT and another is INSERT_ROW. However you are forgetting that your code should evaluate the Event ...
0
Use this map-reduce pattern to calculate the row-by-row sum:
function replaceColumnsWithRowSum() {
const ss = SpreadsheetApp.getActive();
const sourceRange = ss.getRange('Sheet1!C3:D246');
const targetRange = ss.getRange('Sheet1!C3:C246');
const values = sourceRange.getValues();
const rowSum = values.map(row => [row.reduce((sum, value) => sum ...
-1
function addPoints() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('C3:C23').activate();
spreadsheet.getRange('D3:D23').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('D3:D23').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows:...
0
You want to find duplicate values in Column B and delete the record if the corresponding Date and State are identical. If a duplicate exists but either the date and/or state has changed then you want to update the original record for those changed values. In the event of a duplicate with an Internal State of, say, "abc", you also want to delete the ...
1
You can do that with an { array expression }. To weed out blank rows, use query() like this:
=arrayformula(
query(
{
"Date", "Value", "Name";
A2:B, left(B2:B, 0) & B1;
C2:D, left(D2:D, 0) & D1
},
"where Col1 is not null",
1
)
)
If there are many columns in the ...
0
You could use the getSheets method of Class Spreadsheet to get all the sheets of a spreadsheet.
Example
/**
* Writes to the console the name of all the sheets of the active spreadsheet
*/
function logAllSheetsNames(){
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheets = spreadsheet.getSheets();
sheets.forEach(sheet => console.log(...
0
Here's a script that may achieve what you want:
function protectAllSheets(){
const sheet = SpreadsheetApp.getActiveSpreadsheet().getAchiveSheet();
let protection = sheet.protect();
protection.setWarningOnly(true);
}
1
It would probably make sense to re-implement the form as a web app that saves each entry as a row in a spreadsheet.
You may want to take a look at this example:
web app that shows a page with a simple form to enter parameters and show the result
spreadsheet where the data is stored and results are calculated
You can File > Make a copy of the spreadsheet ...
0
One easy solution would be to keep all the data in the same sheet and use filter views to decide which rows to show at a time. This feature lets multiple simultaneous users sort, filter and edit the sheet without disturbing each other.
For additional ease of use, you can insert links in the frozen section of the sheet to easily switch between filter views, ...
Top 50 recent answers are included
