Google script: how to set Array Value on google sheet to specific rows/column

Question

So far I have a code to get files from folder on Google Drive and create a list on Google sheet as

function getFiles() {

  //get value from A1 of every sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetsCount = ss.getNumSheets();
  var sheets = ss.getSheets();
  var rangeHeader = [];
  var range = [];
  var value = [];
  var files = [];

  // for every sheet
  for (var i = 1; i < sheetsCount; i++) {

    value.push(sheets[i].getRange("A1").getValue()); //get cell A1

  }

  // Get files from folder
  for (var i = 0; i < value.length; i++) { // For all the id on the main sheet

    if (value[i] != "") {
      sheets[i + 1].appendRow(["File Name", "URL", "Last Updated", "File Type", "Description"]);
      files = DriveApp.getFolderById(value[i]).getFiles();
      while (files.hasNext()) { // While there is files
        var file = files.next(); // Look on the next file
        var Name = file.getName();
        var url = file.getUrl();
        var date = file.getLastUpdated();
        var type = file.getMimeType();
        var des = file.getDescription();

        sheet[i + 1].appendRow(Name, url, date, type, des);
      }
    }
  }

}

I got a result perfectly on the sheet like this

and then I create a function to update a list by delete a value on column A:E and run getFiles function again

BUT when I add value to another column like this

the header row[File Name,url,Last updated,..] move to the 4th row of the sheet,but I want it to append on the 2nd row everytime I run the function.

and I added the value to that column because it could have another information that I'll add it manually in the future.

So,I would like to ask how can I set value or appendRow to the specific row(2nd row) /column A to E on my script? thanks.

Pierre-Marie Richard Pierre-Marie Richard · Accepted Answer · 2017-07-19T11:55:39

As see on the documention, the appendRow() method put data after the last row. If, in your example, the method put your first lines on fourth row cause of the data on range G2:H3.

To start at second row, you should use the getRange() method and set data like :

var range = sheet.getRange(2, 1, 1, 5); // will get range A2:E2 for the header

For the rest of the data, if absolutly want to use appendRow() method, you should consider to get the data from the sheet with the getDataRange() method, clear the sheet with the clear() method and restore it after you've put the data you want.

Otherwise, you should consider put the data you get from your files on one array and put it at this end with the setValues() method.

Google script: how to set Array Value on google sheet to specific rows/column

1 Answers