I am attempting to run a script that will copy info from multiple different sheets to a different file. I am having issues with the forEach
function. The error I am getting is:
Error
ReferenceError: sheets is not defined
copy @ Code.gs:9
copyInfo @ Code.gs:4
JavaScript
x
35
35
1
function copyInfo() {
2
var sheets = SpreadsheetApp.getActive().getSheets(); // get all sheets
3
sheets.forEach(copy)
4
}
5
6
7
function copy() {
8
var sheetName = sheets.getActiveSheet.getName();
9
if(sheetName.includes("*"))
10
var copySheet = SpreadsheetApp.getActiveSheet();
11
var sheetName=copySheet.getName();
12
var pasteSpreadsheet = SpreadsheetApp.openById("1NXmdq6yCKC6oiDAQNXcovj85697__rCeVJiIT_ou_gI");
13
14
//Create new sheet if one doesn't exist
15
var newSheet = pasteSpreadsheet.getSheetByName(sheetName);
16
17
if (newSheet != null)
18
pasteSpreadsheet.deleteSheet(newSheet);
19
newSheet = pasteSpreadsheet.insertSheet();
20
newSheet.setName(sheetName);
21
22
//Copy and paste data
23
24
// Clear the Google Sheet before copy
25
newSheet.clear({contentsOnly: true});
26
27
// get source range
28
var source = copySheet.getRange("B2:B");
29
var values = source.getValues();
30
// get destination range
31
var destination = newSheet.getRange(1,1,values.length,values[0].length);
32
// copy values to destination range
33
destination.setValues(values);
34
}
35
Advertisement
Answer
If you create a function to use with forEach, that function needs to accept an individual item as a parameter. In this case, each “sheet” from SpreadsheetApp.getActive().getSheets() will be passed as the first parameter into your copy function – but the copy function doesn’t accept any params as is.
JavaScript
1
6
1
function copy(sheet) {
2
var sheetName = sheet.getName();
3
if(sheetName.includes("*"))
4
5
}
6