Google Sheet Display Widget

// The URL of your JSON endpoint
const spreadsheetId = ""


const endpoint = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?tqx=out:json`

// Function that performs the request to the JSON endpoint
async function loadItems() {
	let at = endpoint
	let req = new Request(at)
	let corpo = await req.loadString()
      //log(corpo)
      let json = JSON.parse(corpo.substr(47).slice(0, -2))

log(json)
// We return just the cells
	return json
}
// Request the spreadsheet data
let json = await loadItems()

// Obtaining the content of the exact cell we are looking for
StartBalance = json[4].content["$t"]
CurrentBalance = json[5].content["$t"]
SavingsPercent = json[6].content["$t"]
SavingsAmount = json[7].content["$t"]

// Create the widget
let w = new ListWidget()
let fm = FileManager.iCloud();
let path = fm.documentsDirectory() + "/hsbcbg.png";
w.backgroundImage = fm.readImage(path);

// Add the start balance title to the widget
w.addSpacer(6)
t = w.addText("Banking")
t.textColor = Color.white()
t.font = new Font("San-Fransisco",20)
w.addSpacer(-30)
t = w.addText("__________________________________")
w.addSpacer(13)
t.textColor = Color.white()
t.font = new Font("San-Fransisco",15)
mainStack = w.addStack()
leftStack = mainStack.addStack()
mainStack.addSpacer()
rightStack= mainStack.addStack()
mainStack.layoutHorizontally()
leftStack.layoutVertically()
rightStack.layoutVertically()

// Add the start balance title to the widget
t = leftStack.addText("Start Balance")
t.textColor = Color.white()
t.font = new Font("San-Fransisco",15)

// Add the start balance to the widget
t = leftStack.addText(StartBalance)
t.textColor = new Color("#4CD964")
t.font = new Font("San-Fransisco",13)

leftStack.addSpacer(8)

// Add the current balance title to the widget
t = leftStack.addText("Current Balance")
t.textColor = Color.white()
t.font = new Font("San-Fransisco",15)

// Add the current balance to the widget
t = leftStack.addText(CurrentBalance)
t.textColor = new Color("#4CD964")
t.font = new Font("San-Fransisco",13)

// Add the current savings percent title to the widget
t = rightStack.addText("% Saved")
t.rightAlignText()
t.textColor = Color.white()
t.font = new Font("San-Fransisco",15)

// Add the current savings percent to the widget
t = rightStack.addText(SavingsPercent)
t.rightAlignText()
//t.textColor = new Color("#4CD964")
//t.font = new Font("San-Fransisco",13)

rightStack.addSpacer(8)

// Add the current savings title to the widget
t = rightStack.addText("£ Saved")
t.rightAlignText()
let string = SavingsPercent
let reg = /\-/
let negative = reg.test(string)
if (negative){
  t.textColor = new Color("#ff0000")
}else{
  t.textColor = new Color("#4CD964")
}
t.font = new Font("San-Fransisco",13)

//t.textColor = Color.white()
//t.font = new Font("San-Fransisco",15)

// Add the current savings to the widget
//t = rightStack.addText(SavingsAmount)
//t.rightAlignText()
//t.textColor = new Color("#4CD964")
//t.font = new Font("San-Fransisco",13)

// Add the current savings to the widget_

t = rightStack.addText(SavingsAmount)
t.rightAlignText()
negative = reg.test(SavingsAmount)
if (negative){
  t.textColor = new Color("#ff0000")
}else{
  t.textColor = new Color("#4CD964")
}
t.font = new Font("San-Fransisco",13)

// Position bottom text
w.addSpacer()

w.presentMedium()