Automatically create a new sheet with data of a row in the mastersheet

user_15514183 0
Viewed 61

Consider I have a mastersheet with names in column A and a link in colum B, starting in row 2.

The code below, for a reason I don't understand, creates only one new sheet with the name in row 2, and sets the value of the row 3 in the newly created sheet.

I'd like that to work as followed :

  • create a sheet with the name in col A
  • set values of col A and col B in the newly created sheet

Can someone help me figure out what's going on ? I read the documentation about loops and getRange but I can't figure it out..

Thank you very much

function createNewSheets() {
  var dataSheet = SpreadsheetApp.getActiveSpreadsheet();
  var values = dataSheet.getSheetByName('mastersheet').getRange('A2:B').getValues();
  
  // Get sheet with current swimmer name:
  var sheet = dataSheet.getSheetByName(swimmerName);

  var col = values[i]; // Current row
  var swimmerName = col[0]; // swimmer name

  // Iterate through all rows of data from "mastersheet":
  for (var i = 1; i < values.length; i++) {

    // Check if sheet with current swimmer name exists. If it doesn't it creates it:
    if (!sheet) {
      sheet = dataSheet.insertSheet(swimmerName);
    }
    
    // Sets current row of data to the new sheet:
    sheet.getRange(1, 1, col.length, col[0].length).setValues(col)
  }
}
google-apps-script google-sheets for-loop
1 Answers

I believe your goal as follows.

  • You want to retrieve the values from the columns "A" and "B" from "mastersheet" sheet.
  • You want to use the values of column "A" retrieved from "mastersheet" sheet as the sheet names.
  • When the sheet of the retrieved sheet name is not existing in the active Spreadsheet, you want to insert new sheet with the sheet name.
  • When the new sheet is inserted, you want to copy the same row of the sheet name to the inserted sheet.
  • You want to achieve this using Google Apps Script.

Modification points:

  • In your script,

    • At var sheet = dataSheet.getSheetByName(swimmerName), swimmerName is not declaread.
      • When swimmerName is not declared elsewhere, an error occurs at this line.
    • At var col = values[i], i is not declared.
    • In the for loop,
      • the 1st index is 1. From getRange('A2:B'), in this case, the values are retrieved from 3rd row.
      • swimmerName and col are not changed.

  • In order to achieve your goal, I would like to propose the following flow.

    1. Retrieve the current sheet names.
    2. Retrieve the values from "mastersheet" sheet.
    3. Using the retrieved sheet names and values, the new sheets are inserted and the values are put.

When above flow is reflected to a script, it becomes as follows.

Sample script:

function createNewSheets() {
  // 1. Retrieve the current sheet names.
  var dataSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetNames = dataSheet.getSheets().map(s => s.getSheetName());

  // 2. Retrieve the values from "mastersheet" sheet.
  var masterSheet = dataSheet.getSheetByName('mastersheet');
  var values = masterSheet.getRange('A2:B' + masterSheet.getLastRow()).getValues();

  // 3. Using the retrieved sheet names and values, the new sheets are inserted and the values are put.
  values.forEach(r => {
    if (!sheetNames.includes(r[0])) {
      dataSheet.insertSheet(r[0]).appendRow(r);
      sheetNames.push(r[0]);
    }
  });
}
  • In this sample script, sheetNames is growing in the loop and that is used for checking the duplicate sheet names.

References:

user_71086530
Related