CODEWITHSUNDEEP
jsongoogle-apps-scriptgoogle-sheets
Olga Ivanova
Olga Ivanova

Reputation: 41

How to read entire google spreadsheet as a text file via google script (gs) (by rows)

I have a google sheet with a json formatted text sitting at random cells, need to read it by google app script. There are ways to read it by columns or as an array which actually inserts unnecessary semicolons. Please advise how to do it similar to UrlFetchApp.fetch and JSON.parse but to read directly from the spreadsheet by rows, skipping empty cells?

E.g. A1 B1 C1 ... A2 B2 C2 ... etc. (without spaces)

Upvotes: 0

Views: 63

Answers (1)

GoranK
GoranK

Reputation: 1668

function readJson(){
  const sh = SpreadsheetApp.getActive().getSheetByName("Sheet1")
  const allValues = sh.getDataRange().getValues()
  const arrJson = []
  allValues.forEach(row=>{
    row.filter(cell=>cell).forEach(cell=>{
      try{
        const json = JSON.parse(cell)
        arrJson.push(json)
      } catch(ignore){
        // JSON parse failed, probably not JSON
      }
    })
  })

  // arrJson contains all json data from cells in Sheet1
}

Upvotes: 1

Related Questions