0

I had some code created using the classic editor that did the job of inserting 10 lines in two Google Sheets, which grow down over time with record building. The code speeded things up for the users. But, it quit working properly. I Spent about 6 hours researching and learning how to do vague trial and erroring testing with no success. I upgraded to the new editor. It took about 12 hours to lean the new interface and rewrite to speed the new macro up using the things that I have learned over the last few years with Google Script and Sheets. Adapted macro to two sheets (Amazon and eBay) to be fully automated, but I've run into a road block on the second to last line (a line of the original code) with an error (above) using the new editor . I'm under a deadline and trying to get Amazon working again first. I've just finished proving to myself where the roadblock is. I did find one reference that indicated I may need to load and API. But, Google searches aren't coming up with the same find.

function Insert_One_Row(){
  var ss = SpreadsheetApp.getActive();                      //"spreadsheet"/Spreadsheet
  logger.log(ss);
  var as = SpreadsheetApp.getActiveSpreadsheet();           //"sheet"/Sheet/activesheet
  Logger.log(as);
  var asn= ss.getName();                                    //"sheet"?/activesheetname
  Logger.log(asn);
  var ac = ss.getCurrentCell();                             //currentCell/activecell
  Logger.log(ac);
  var arw= ac.getRow();                                     //activeRow
  Logger.log(arw);
  var arg= ss.getActiveRange();                             //activeRange
  Logger.log(arg);
}
  if(asn = 'Amazon'){
    nr.activate();
  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();  //for ReferenceError: ss 
  //was not defined below
  var Sheet = SpreadsheetApp.getActiveSpreadsheet();  //for ReferenceError: ss was not 
  //defined below
  //Errored on SS
    Logger.log(Spreadsheet);
    Sheet.getRange(Spreadsheet.getCurrentCell().getRow() -2, 1, 1, Sheet.getMaxColumns()).activate(); 
    Insert_Row();
  }

2 Answers 2

1

The Sheets have assigned Class Spreadsheet object but this class hasn't the getMaxColumn method. This method is available for Class Sheet and Class Range objects.


NOTES:

Please bear in mind that var asn= ss.getName(); assigns the spreadsheet name to the asn variable. If you want to assign the name of the active sheet, instead use

var sheet = SpreadsheetApp.getActiveSheet();
var asn = sheet.getName();`

It's very likely that asn = 'Amazon' is wrong. Instead use asn === 'Amazon'.

Based on the common style guides, i.e. Google JavaScript Style Guide, don't not use title case, i.e. Spreadsheet or Sheet for naming variables... this will help you to avoid problems when using certain features like the Google Apps Script Advanced Sheets Service and make it easier for others to help you.

Reference

0

Try this. It is based on your modified code.

A few things to note:

  • I don't know the definition of the range "EOD"
  • You don't need to declare the number of columns (getmaxColumns()). It's sufficient to declare a range consistenting of just the row and column reference.
  • It's not clear why you deduct "2" from the first row of the named range, and INSERT_ROW is a function that you have written and haven't included. So I changed this to as.insertRowAfter((ac.getRow()-2)) which inserts a single blank row after the row that is 2 rows above "EOD".

//Get EOD range, select, index up 2 rows to start row insertions. For now the 
  //User places cursor on the row number in the row above the orange line and starts this macro
  //until I can use the named cell to do that automatically
  function Insert_One_Row(){
    /*
    // Define Variables
    // as = "Sheet" Functions, ss = "Spreadsheet" Functions, as = ActiveSpreadsheet, ac = activecell
    // ar = activerow, r = Range, asn = ActiveSheetName, nr =NamedRanged
    */
    var ss = SpreadsheetApp.getActiveSpreadsheet()  //Spreadsheet
    var as = ss.getActiveSheet();                   //Sheet

    //Assigns the spreadsheet name to a variable
    var asn= as.getName();                          //ActiveSheetName
    Logger.log(asn);


    // test for "Amazon"
    if(asn == 'Amazon'){              //Passes run test with var asn= ss.getName();
    
      var nr = ss.getRangeByName('EOD')
      nr.activate()
      var ac = as.getCurrentCell();                  //CurrentCell
      Logger.log("current cell = "+ac.getA1Notation())
    

      Logger.log("DEBUG: get Range parameters for row insert:")
      Logger.log("DEBUG: row="+(ac.getRow()-2))
      Logger.log("DEBUG: Column=1 (Column A)")

      var rangeforinsert = as.getRange((as.getCurrentCell().getRow()-2),1)
      Logger.log("The insert range is "+rangeforinsert.getA1Notation())

      // This inserts a row after the first row position
      as.insertRowAfter((ac.getRow()-2))
      Logger.log("Inserted row")

    }
  }
2
  • Ernie, are you suggesting that if(asn = 'Amazon'){ should be changed to if(asn == 'Amazon'){ ? Tell the truth, I saw Rubén's suggestion for asn === 'Amazon' but 1) I forgot to include it, and 2) I was trying to use as much of your code as possible. My (limited) testing shows that the code works with all three, but I have no problem with the change that you suggest.
    – Tedinoz
    Jun 10 at 1:50
  • If you think that this has been a good answer, then you might consider accepting it.
    – Tedinoz
    Jun 10 at 1:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.