change script to overwrite to same sheet?

[Brian Pugh]

I found the script below that writes names and URL's of all the files in a single directory. It works very well, but I need to modify it so that it writes to the same sheet every time, overwriting/updating the data on the same tab.

I do not know how to change it. 

Hoping that one of you will kindly show me.

Thank you very much.

Brian

function LISTING() {

  let Folders = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents()

  Logger.log(Folders)

  let result = [];

  while(Folders.hasNext()){

    let Folder = Folders.next()

    let Files = Folder.getFiles()

    while(Files.hasNext()){

      let File = Files.next()

      result.push([File.getUrl(),File.getName(),File.getMimeType()])

    }

  }

  Logger.log(result)

  SpreadsheetApp.getActiveSheet().getRange(1,1,result.length,result[0].length).setValues(result)

}

[cwl...@gmail.com]

This should write to the same tab you define in whatever spreadsheet this script resides in: function LISTING() {

  let Folders = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents()

let ss = SpreadsheetApp.getActiveSpreadsheet(); //get the current spreadsheet

let ws = ss.getSheetByName("YOUR_SHEET/TAB_NAME") //get the desired sheet/tab

  Logger.log(Folders)

  let result = [];

  while(Folders.hasNext()){

    let Folder = Folders.next()

    let Files = Folder.getFiles()

    while(Files.hasNext()){

      let File = Files.next()

      result.push([File.getUrl(),File.getName(),File.getMimeType()])

    }

  }

  Logger.log(result)

ws.getDataRange.clearContent() //clear whatever contents are on the sheet/tab

   ws.getRange(1,1,result.length,result[0].length).setValues(result)

[cwl...@gmail.com]

oops.. small error. I forgot to add "()" after getDataRange. The second to last line should be:

ws.getDataRange().clearContent() //clear whatever contents are on the sheet/tab

[Brian Pugh]

I really appreciate your help. Thank you!

Getting one error:

TypeError: ws.getDataRange.clearContent is not a function

LISTING

@ Code.gs:16

Brian Pugh, IT/Educational Technologies Associated Hebrew Schools | Danilack Middle School p: 416.494.7666, | e: bp...@ahschools.com w: www.associatedhebrewschools.com 252 Finch Ave W., Toronto, ON M2R 1M9

This email is confidential and is intended for the above-named recipient(s) only. If you are not the intended recipient, please delete this email from your system. Any unauthorized use or disclosure of this email is prohibited.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/57efc2c0-c6a9-4f76-b817-8b7972c3c19bn%40googlegroups.com.

[cwl...@gmail.com]

see my second reply...  ;)     add a "()" after the getDataRange   so it reads:   

ws.getDataRange().clearContent()

[Brian Pugh]

This what I now have:

function LISTING() {

  let Folders = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents()

  let ss = SpreadsheetApp.getActiveSpreadsheet();  //get the current spreadsheet

  let ws = ss.getSheetByName("List of All Kamin IEP's")  //get the desired sheet/tab

  Logger.log(Folders)

  let result = [];

  while(Folders.hasNext()){

    let Folder = Folders.next()

    let Files = Folder.getFiles()

    while(Files.hasNext()){

      let File = Files.next()

      result.push([File.getUrl(),File.getName(),File.getMimeType()])

    }

  }

  Logger.log(result)

    ws.getDataRange().clearContent()  //clear whatever contents are on the sheet/tab

    ws.getRange(1,1,result.length,result[0].length).setValues(result)

}

Now returning this error:

TypeError: Cannot read properties of null (reading 'getDataRange')

LISTING @ Code.gs:16

I'm very sorry to be a nuisance.

Brian

Brian Pugh, IT/Educational Technologies Associated Hebrew Schools | Danilack Middle School p: 416.494.7666, | e: bp...@ahschools.com w: www.associatedhebrewschools.com 252 Finch Ave W., Toronto, ON M2R 1M9

This email is confidential and is intended for the above-named recipient(s) only. If you are not the intended recipient, please delete this email from your system. Any unauthorized use or disclosure of this email is prohibited.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/65754485-4fd1-4553-ab0c-b0b98e827664n%40googlegroups.com.

[Brett Grear]

If the sheet is completely blank there would be no data to replace and therefore the script would crash.  Just add a couple random bits of data in any cell to ensure there is a dataRange to find and then the script should work correctly.

[Brian Pugh]

Thank you, Brett, but the sheet has many rows of data (A1:B36).

Brian Pugh, IT/Educational Technologies Associated Hebrew Schools | Danilack Middle School p: 416.494.7666, | e: bp...@ahschools.com w: www.associatedhebrewschools.com 252 Finch Ave W., Toronto, ON M2R 1M9

This email is confidential and is intended for the above-named recipient(s) only. If you are not the intended recipient, please delete this email from your system. Any unauthorized use or disclosure of this email is prohibited.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/66bce33e-c4ae-4ec0-be63-e3bd1b2b328en%40googlegroups.com.

[Web Dev]

Hi Brian. I can confirm the provided script works well on my end. Please make sure you have the same copy of the script and your sheets (tabs) are correctly named. Feel free to share a copy for a review.

Alex

[Brett Grear]

The issue is likely to be with this line then:

 let ws = ss.getSheetByName("List of All Kamin IEP's")  //get the desired sheet/tab

Are you sure the Sheet Name is correct? Can you shorten the Sheetname for simplicities sake. It could be the apostrophe type is not the same on both versions. Straight and curly apostrophes have a different ASCII code.