Here's the scenario: I have a multiple tab (sheet) worksheet. Sheet A (Rounds) collects data and sheets B-D format that data in different ways. I am trying to create a script that will set the current sheet to sheet D (Transfer-Stats) then create a 2D array of the data there. I then want to scan through that array and select the non-blank rows and place them in another 2D array which will then be added (appended) to a different worksheet. I'm a beginner in Apps Script and beyond my technical ability. I would like some basic guidance. I have gotten some preliminary code to work (shown below) but the only thing I've accomplished so far is to find the last row and column of the Transfer-Stats sheet. When it comes to the statement that attempts to create the first 2D array it errors with "Cannot find method getRange(number,number).". Any help would be appreciated on how to code this problem.
function saveStats() {
ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssName = SpreadsheetApp.getActiveSpreadsheet().getName();
var ssRounds = ss.getSheetByName('Rounds').getName();
var ssStats = ss.getSheetByName('Transfer-Stats').getName();
ui.alert("ssRounds " ssRounds " ssStats " ssStats " " ssName) /** names are correct */
SpreadsheetApp.setActiveSheet(ss.getSheetByName(ssStats), true); /** set to Transfer-Stats */
var lastStatsRow = ss.getLastRow() /** find last row of stats sheet */
var lastStatsCol = ss.getLastColumn() /** find last column of stats sheet */
var statsArray = [ss.getRange(1, lastStatsCol).getValues()]
ui.alert("Stop at Stats Last row is " lastStatsRow " Last Row is " lastStatsCol " " ssStats " " ss)
/** data shown above is: Stop at Stats Last row is 25 Last Col is 19 Transfer-Stats Spreadsheet which is correct for last row and column but not sure about "Spreadsheet" */
var statsArray = [ss.getRange(1, lastStatsCol).getValues()] /** this statement fails */
// SpreadsheetApp.setActiveSheet(ss.getSheetByName(ssRounds),true);
// ssStats.activate()
}
A little more detail: My primary sheet (Rounds) contains the data provided by the user. The Transfer-Stats sheet collects some of that data using equated cells from the Rounds sheet (no data input to Transfer-Stats). The script being developed will be initiated from the Rounds sheet but I want to collect the rows from Transfer-Stats and create a filtered array to be appended to a 3rd sheet not in this spreadsheet. I would share the sheets on this forum if I knew how - no sensitive data on them. I tried a modified version of the suggested code as follows:
function saveStats() {
// ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssName = SpreadsheetApp.getActiveSpreadsheet().getName();
var ssRounds = ss.getSheetByName('Rounds').getName();
var ssStats = ss.getSheetByName('Transfer_Stats').getName(); /** my code */
ssStats = SpreadsheetApp.setActiveSheet(ss.getSheetByName(ssStats),true); /** set to Transfer_Stats */
var lastStatsRow = ss.getLastRow() /** find last row of Transfer_Stats sheet */
var lastStatsCol = ss.getLastColumn() /** find last column of Transfer_Stats sheet */
var nameCol = 2 /** this column contains player name (checked for blank) */
var rowNum = 2 /** starting row */
var filterArray = [] /** temporary array for filtered rows */
var timeStamp = Utilities.formatDate(new Date(), "MST", "yyyy-MM-dd'/'HH:mm:ss") ; /** stamp each new row */
var filterSheet = ss.getSheetByName("Filtered_Stats") /** see if filtered sheet exists in the spreadsheet */
if (filterSheet != null) ss.deleteSheet(filterSheet) ; /** if it does, delete it so next statement will work */
filterSheet = ss.insertSheet("Filtered_Stats") /** create a new Filtered_Stats sheet */
var filterSheet = ss.getSheetByName('Filtered_Stats').getName(); /** get the name */
/** Ignore these statements - they will become a question for later ? */
// statsDB = SpreadsheetApp.openById("SVE_Stats_Fall_2021"); /** get the stats database */
// statsDB = DriveApp.getFile /** ById("SVE_Stats_Fall_2021") ; /** get the stats database */
// while (statsDB.hasNext()) {
// var file = statsDB.next();
// Logger.log(file.getName());
// }
// Logger.log(statsDB)
// var myID = sheet.getRange(lastRow, myIDCol).getValue(); /** sample from web */
// ui.alert("Stop at Stats Last row is " lastStatsRow " Last Col is " lastStatsCol " ssStats " ssStats " ss " ss)
// var statsArray = ssStats.getRange(2, 1 ,lastStatsRow, lastStatsCol).getValues() /** .filter(r >= r.join("")) */
/** Resume here with executable code */
for (var rowNum=2; rowNum < lastStatsRow 1; rowNum ) /** start selecting rows from the 2D array */
{
var statsName1 = ssStats.getRange(rowNum, nameCol).getValues() ; /** get player 1 name for this row to check if empty */
var statsName2 = ssStats.getRange(rowNum, nameCol 1).getValues() ; /** get player 2 name for this row to check if empty */
var statsArray = ssStats.getRange(rowNum, 1, 1, lastStatsCol).getValues() ; /** get the complete row of data in new array */
statsArray.unshift(timeStamp) ; /** add a time stamp as the first cell of the array */
Logger.log(statsArray)
/** here's a sample from the above Logger.log (the first row of the 2D array with timestamp added):
(the second date & time in the sample is actually a date in the original row)
7:30:34 AM Info [2021-10-10/04:30:33, [R1M1-A, Ann Anderson, Kathy Biccum, 1.5, None, , Mon Jan 01 01:00:00 GMT-07:00 2001, 6.0, 4.0, 0.0, 6.0, 1.0, 5.0, 10.0, 0.0, 0.0, 17.0, 6.0, 35.294117647058826]] */
/** do I see a 2D array here because of the 2 sets of brackets [] ? */
if (statsName1 != "" && statsName2 != "") {
filterArray.push(statsArray) /** attempt to create a row in the 1D array from the 2D array */
Logger.log(filterArray) /** show what the new array looks like */
/** here's a sample of what the new array looks like:
7:39:50 AM Info [[2021-10-10/04:39:49, [R1M1-A, Ann Anderson, Kathy Biccum, 1.5, None, , Mon Jan 01 01:00:00 GMT-07:00 2001, 6.0, 4.0, 0.0, 6.0, 1.0, 5.0, 10.0, 0.0, 0.0, 17.0, 6.0, 35.294117647058826]]] */
/** the new array now has another set of brackets around it */
Logger.log(filterArray.length) /** show how long it is */
/** here's the length at this point which is correct for the first iteration of the for loop:
7:44:02 AM Info 1.0 */
/** the for loop executes correctly, selecting all the rows with non-blank names and placing them in the new array */
/** problem is the end result appended to the new sheet (see below) contains only 2 data elements not 20 as expected */
/** this code attempts to switch to the Filter_Stats sheet and populate a row from the filterArray */
/** it somewhat works, the first cell of each row in the sheet is filled with the timestamp */
/** but the second cell of each row in the sheet contains [Ljava.lang.Object;@39e47f39 */
ssFiltr = SpreadsheetApp.setActiveSheet(ss.getSheetByName(filterSheet),true); /** set to Filter_Stats */
ssFiltr.appendRow(statsArray) ;
ssStats = ss.getSheetByName('Transfer_Stats').getName();
ssStats = SpreadsheetApp.setActiveSheet(ss.getSheetByName(ssStats),true); /** reset to Transfer_Stats */
}
}
// SpreadsheetApp.setActiveSheet(ss.getSheetByName(ssRounds),true); /** return to primary sheet Rounds */
}I know this post is becoming confusing but here's where I am to date. I have some code that works to a degree, albeit rather crude, but it doesn't completely do what I want. I've included the code with many comments embedded showing detailed samples of data as I run the code. I think the primary problem is that I'm not handling the arrays correctly and as a result the row that gets created in the new sheet does not contain what I expect. Any help would be appreciated.
CodePudding user response：
I believe your goal is as follows.
- You want to retrieve the values from the sheet "Rounds", and want to remove the empty rows from the values. And, you want to append the values to "Transfer-Stats".
In this case, how about the following sample script?
Sample script:
I added the comments for each line. Please check it. And, before you use this script, please set the destination sheet name.
function saveStats() {
// Retrieve Spreadsheet.
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Retrieve sheet "Transfer-Stats".
var ssStats = ss.getSheetByName('Rounds');
// Retrieve values from "Transfer-Stats" and remove the empty rows.
var values = ssStats.getRange(1, 1, ssStats.getLastRow(), ssStats.getLastColumn()).getValues().filter(r => r.join(""));
// Retrieve sheet "Transfer-Stats".
var dstSheet = ss.getSheetByName('Transfer-Stats'); // Please set the destination sheet name.
// Append the values to the sheet "Transfer-Stats"
dstSheet.getRange(dstSheet.getLastRow() 1, 1, values.length, values[0].length).setValues(values);
}
Note:
- I understood that you wanted to copy the values from "Rounds" sheet to "Transfer-Stats" sheet. If I misunderstood the sheet names, please modify the above script.
References:
Edit:
From your following replying,
A little more detail: My primary sheet (Rounds) contains the data provided by the user. The Transfer-Stats sheet collects some of that data using equated cells from the Rounds sheet (no data input to Transfer-Stats). The script being developed will be initiated from the Rounds sheet but I want to collect the rows from Transfer-Stats and create a filtered array to be appended to a 3rd sheet not in this spreadsheet. I would share the sheets on this forum if I knew how - no sensitive data on them.
I couldn't notice this. In this case, I understand that you want to retrieve the data from Transfer-Stats sheet and want to put the filtered values to a sheet (I cannot known the sheet name) except for Transfer-Stats and Rounds. If my understanding is correct, how about the following sample script?
Sample script:
function saveStats() {
// Retrieve Spreadsheet.
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Retrieve sheet "Transfer-Stats".
var ssStats = ss.getSheetByName('Transfer-Stats');
// Retrieve values from "Transfer-Stats" and remove the empty rows.
var values = ssStats.getRange(1, 1, ssStats.getLastRow(), ssStats.getLastColumn()).getValues().filter(r => r.join(""));
// Retrieve sheet a sheet I cannot known the sheet name.
var dstSheet = ss.getSheetByName('###'); // Please set the destination sheet name.
// Append the values to the sheet I cannot known the sheet name.
dstSheet.getRange(dstSheet.getLastRow() 1, 1, values.length, values[0].length).setValues(values);
}
CodePudding user response：
Here's the code that works for me.
function saveStats() {
// ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssName = SpreadsheetApp.getActiveSpreadsheet().getName();
var ssRounds = ss.getSheetByName('Rounds').getName();
var ssStats = ss.getSheetByName('Transfer_Stats').getName(); /** my code */
ssStats = SpreadsheetApp.setActiveSheet(ss.getSheetByName(ssStats),true); /** set to Transfer_Stats */
var lastStatsRow = ss.getLastRow() /** find last row of Transfer_Stats sheet */
var lastStatsCol = ss.getLastColumn() /** find last column of Transfer_Stats sheet */
var nameCol = 2 /** this column contains player name (checked for blank) */
var rowNum = 2 /** starting row */
var filterArray = [] /** temporary array for filtered rows */
var timeStamp = Utilities.formatDate(new Date(), "MST", "yyyy-MM-dd'/'HH:mm:ss") ; /** stamp each new row */
var filterSheet = ss.getSheetByName("Filtered_Stats") /** see if filtered sheet exists in the spreadsheet */
if (filterSheet != null) ss.deleteSheet(filterSheet) ; /** if it does, delete it so next statement will work */
filterSheet = ss.insertSheet("Filtered_Stats") /** create a new Filtered_Stats sheet */
var filterSheet = ss.getSheetByName('Filtered_Stats').getName(); /** get the name */
/** Ignore these statements - they will become a question for later ? */
// statsDB = SpreadsheetApp.openById("SVE_Stats_Fall_2021"); /** get the stats database */
// statsDB = DriveApp.getFile /** ById("SVE_Stats_Fall_2021") ; /** get the stats database */
// while (statsDB.hasNext()) {
// var file = statsDB.next();
// Logger.log(file.getName());
// }
// Logger.log(statsDB)
// var myID = sheet.getRange(lastRow, myIDCol).getValue(); /** sample from web */
// ui.alert("Stop at Stats Last row is " lastStatsRow " Last Col is " lastStatsCol " ssStats " ssStats " ss " ss)
// var statsArray = ssStats.getRange(2, 1 ,lastStatsRow, lastStatsCol).getValues() /** .filter(r >= r.join("")) */
/** Resume here with executable code */
for (var rowNum=2; rowNum < lastStatsRow 1; rowNum ) /** start selecting rows from the 2D array */
{
var statsName1 = ssStats.getRange(rowNum, nameCol).getValues() ; /** get player 1 name for this row to check if empty */
var statsName2 = ssStats.getRange(rowNum, nameCol 1).getValues() ; /** get player 2 name for this row to check if empty */
var statsArray = ssStats.getRange(rowNum, 1, 1, lastStatsCol).getValues() ; /** get the complete row of data in new array */
statsArray.unshift(timeStamp) ; /** add a time stamp as the first cell of the array */
Logger.log(statsArray)
/** here's a sample from the above Logger.log (the first row of the 2D array with timestamp added):
(the second date & time in the sample is actually a date in the original row)
7:30:34 AM Info [2021-10-10/04:30:33, [R1M1-A, Ann Anderson, Kathy Biccum, 1.5, None, , Mon Jan 01 01:00:00 GMT-07:00 2001, 6.0, 4.0, 0.0, 6.0, 1.0, 5.0, 10.0, 0.0, 0.0, 17.0, 6.0, 35.294117647058826]] */
/** do I see a 2D array here because of the 2 sets of brackets [] ? */
if (statsName1 != "" && statsName2 != "") {
filterArray.push(statsArray) /** attempt to create a row in the 1D array from the 2D array */
Logger.log(filterArray) /** show what the new array looks like */
/** here's a sample of what the new array looks like:
7:39:50 AM Info [[2021-10-10/04:39:49, [R1M1-A, Ann Anderson, Kathy Biccum, 1.5, None, , Mon Jan 01 01:00:00 GMT-07:00 2001, 6.0, 4.0, 0.0, 6.0, 1.0, 5.0, 10.0, 0.0, 0.0, 17.0, 6.0, 35.294117647058826]]] */
/** the new array now has another set of brackets around it */
Logger.log(filterArray.length) /** show how long it is */
/** here's the length at this point which is correct for the first iteration of the for loop:
7:44:02 AM Info 1.0 */
/** the for loop executes correctly, selecting all the rows with non-blank names and placing them in the new array */
/** problem is the end result appended to the new sheet (see below) contains only 2 data elements not 20 as expected */
/** this code attempts to switch to the Filter_Stats sheet and populate a row from the filterArray */
/** it somewhat works, the first cell of each row in the sheet is filled with the timestamp */
/** but the second cell of each row in the sheet contains [Ljava.lang.Object;@39e47f39 */
ssFiltr = SpreadsheetApp.setActiveSheet(ss.getSheetByName(filterSheet),true); /** set to Filter_Stats */
ssFiltr.appendRow(statsArray) ;
ssStats = ss.getSheetByName('Transfer_Stats').getName();
ssStats = SpreadsheetApp.setActiveSheet(ss.getSheetByName(ssStats),true); /** reset to Transfer_Stats */
}
}
// SpreadsheetApp.setActiveSheet(ss.getSheetByName(ssRounds),true); /** return to primary sheet Rounds */
}