Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Importing CSV but I only want to base the insert point on the last row in Column A

I have an app script that imports CSV files to columns A:E. The problem I have is that if I add any data to say Column H, then my app script starts the import below that data instead of below the last row of data in the actual columns that I am importing into. I need the script to only judge the data in the first 4 columns.
I tried range = sheet.getRange(2,1,sheet.getLastRow())
Here is the code I am using.

function writeDataToSheet(data) {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName("Import");
    //const range = sheet.getRange(sheet.getLastRow()+1,1,data.length,data[0].length).setValues(data);
    const range = sheet.getRange(2,1, sheet.getLastRow()+1, data.length, data[0].length).setValues(data);
    return sheet.getName();
  }
Col A Col B Col C Col D Col E …. Col H
DATA DATA DATA DATA DATA ….
DATA DATA DATA DATA DATA ….
DATA DATA DATA DATA DATA ….
DATA DATA DATA DATA DATA ….
DATA
DATA
DATA DATA DATA DATA DATA ….
DATA DATA DATA DATA DATA ….
DATA DATA DATA DATA DATA ….
DATA DATA DATA DATA DATA ….

>Solution :

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Modification points:

  • In the case of const range = sheet.getRange(2,1, sheet.getLastRow()+1, data.length, data[0].length).setValues(data);, 5 arguments are used in the method of getRange. In this case, 4 arguments are used.
  • In your script, it seems that range is not used.
  • In your situation, how about checking the last row of the column "A"?

When these points are reflected in your script, how about the following modification?

Modified script:

function writeDataToSheet(data) {
  // ref: https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() + 1 : offsetRow;
  };
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Import");
  sheet.getRange(sheet.get1stNonEmptyRowFromBottom(1), 1, data.length, data[0].length).setValues(data);
  return sheet.getName();
}
  • By this modification, the values of data is put from the next row of the last row of column "A".
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading