Google-sheets – Get table name with apps script

google sheetsgoogle-apps-script

I have two tables, namely Table A and Table B. Each table I want to identify the name of the table in column C matches the table names that have been defined in column A.

The script I've made is like this:

tableName () function {
  let ss = SpreadsheetApp.getActiveSpreadsheet ();
  let cColumn1 = ss.getRange ('C3: C7');
  let cColumn2 = ss.getRange ('C11: C15');
  
  let tableName1 = ss.getRange ('C2'). getValue ();
  let tableName2 = ss.getRange ('C9'). getValue ();
  
  cColumn1.setValues   (tableName1);
  cColumn2.setValues   (tableName2);
}

But my script is still manual, we have to specify the start line and end line of the table.

To identify the name of the table I've ever made with a formula, so with this formula, we don't need to specify the position the name of the reference table.

= ARRAYFORMULA (IF ((B: B = "") + (B: B = "Name") ,, VLOOKUP (ROW (A: A), IF (LEN (REGEXEXTRACT (A: A, "T\D+") ), {ROW (A: A), REGEXEXTRACT (A: A, "T\D+")}, 0), 2)))

This formula is very heavy if the amount of data is large.

My Spreadsheet.

Best Answer

You have Table A and Table B; they are stacked, one above the other in columns A, B and C and there is no other data in Columns A, B or C. Each table may be any number of rows long and there is a single blank row between each table. The name of Table A is in cell A1; the name of Table B is in Column A in the row following the blank row after Table A.

You want to populate cells in Column C with the respective table names. You have a formula (Cell E1) but this formula takes too long if there is a lot of data. So you want a script to populate cells in Column C and the script must dynamically calculate the ranges that apply to each Table.

Though the script in your question is manually updated, it fails because setValues() expects a two dimensional array (the values must match the dimensions of the range) but your input value is the value of a single cell.

Points to note:

  • The key column is Column A. getLast Row() is not used because it will return the last row for the entire sheet and this does not necessarily represent the data in Column A.
  • the Javascript filter method is used ("ALast") to determine the last row for only Column A.
    • Since there is a blank row in Column A (which is ignored by the filter method), a value of 1 (one) must be added to "ALast" to determine the actual last row of content.
  • the content of Column A is analysed through a loop;
    • a table counter is used to distinguish between table 1 and table 2.
  • the output is added to temporary arrays and the ranges are updated with the setValues() method.
    • the Javascript fill method is used to build the temporary arrays since the content for each array is multiple instances of the same value.
function wa14315803wa() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "wa_143158"
  var sheet = ss.getSheetByName(sheetname);

  // calculate the number of rows in Column A
  var Avals = sheet.getRange("A1:A").getValues();
  var Alast = Avals.filter(String).length;
  //Logger.log("DEBUG: the last row in column A = "+(+Alast+1))

  // range variables
  var outputCol=3;
  var table1StartRow=3;

  // Table counter
  var table = 0;  

  // there are no blank rows except for between table A and Table B
  // so it is safe to start at row 0
  for (i=0;i<Alast+2;i++){

    // test for empty row and table counter = 0 (Table 1)
    if (Avals[i][0] == "" && table==0){
      // this is table A (the first table)
      // row is empty
      // table A ending range = i (since rows start at 0)

      // get the Table name from cell A1
      var tableName1 = sheet.getRange(1,1).getValue();
      // Logger.log("DEBUG: Name of Table 1 = "+tableName1);

      var table1EndRow = i;
      var table1NumRows = table1EndRow-table1StartRow+1

      // define the output range for the first table
      var cColumn1 = sheet.getRange(table1StartRow,outputCol,table1NumRows);
      // Logger.log("DEBUG: cColumn1 range = "+cColumn1.getA1Notation());

      // update the output for Table1
      var table1array=[];
      table1array.length = table1NumRows; // set array size
      table1array.fill([tableName1]); // fill array with any value    
      cColumn1.setValues(table1array);

      // increment table counter
      var table = 1; 

      // define the start row for Table 2
      var table2StartRow = i+4;
      //Logger.log("DEBUG: table2StartRow = "+table2StartRow);

    }

    // test for empty row and table counter = 1 (Table 2)
    if (Avals[i][0] == "" && table==1){
      // this is table B (the second table)
      // row is empty

       // get the Table name
      var tableName2 = Avals[(+i+1)][0];
      // Logger.log("DEBUG: Name of Table 2 = "+tableName2);

      var table2EndRow=Alast+1;
      var table2NumRows = table2EndRow-table2StartRow+1
      //Logger.log("DEBUG: table2EndRow="+table2EndRow+",table2StartRow = "+table2StartRow+", table2NumRows = "+table2NumRows);

      // define the output range for the second table
      var cColumn2 = sheet.getRange(table2StartRow,outputCol, table2NumRows);
      // Logger.log("DEBUG: cColumn2 range = "+cColumn2.getA1Notation());

      // update the output for Table2
      var table2array=[];
      table2array.length = table2NumRows; // set array size
      table2array.fill([tableName2]); // fill array with any value   
      cColumn2.setValues(table2array);

    break;
    }

  }

}
Related Topic