In google script, I am using appendRow to put data in a worksheet from another worksheet, however, i-CodePudding

I'm new to programming in general so I simply got my code from youtube, however, appendRow doesn't work, as in it displays inputted info as blank but takes space anyway. I know that it is blank and takes space since I have a formula that displays the name and ID, and surprisingly it correctly displays the name and ID, but there are other information which I didn't include within the formula.

I made the code almost exactly like the one from youtube and has shown to work, until I made the same code but for different information entirely (Financial info). And the appendRow for the financial code works perfectly, so I am very confused as to how the first code isn't working.

Here is the code I use to save an entry:

const ss = SpreadsheetApp.getActiveSpreadsheet()

const forumWS = ss.getSheetByName("Forum")
const behindthescenesWS = ss.getSheetByName("BehindTheScenes")
const dataWS = ss.getSheetByName("Data")

const idCell = forumWS.getRange("C5")
const searchCell = forumWS.getRange("C3")

const fieldRange = ["F5","C7","C9","F7","F9"]

    function SaveRecord() {

  const id = idCell.getValue()
  
  if(id == ""){
    createNewRecord()
    return
  }

  const cellFound = dataWS.getRange("A:A").createTextFinder(id).matchCase(true).matchEntireCell(true).findNext()

  if(!cellFound) return
  const row = cellFound.getRow()
  const fieldValues = fieldRange.map(f => forumWS.getRange(f).getValue())
  fieldValues.unshift(id)
  dataWS.getRange(row,1,1,fieldValues.length).setValues([fieldValues])
  searchCell.clearContent()
  ss.toast("Changes Saved!","ID:"   id)
}

function createNewRecord() {
  const fieldValues = fieldRange.map(f => forumWS.getRange(f).getValue())

  const nextIDCell = behindthescenesWS.getRange("B3")
  const nextID = nextIDCell.getValue()
  fieldValues.unshift(nextID)

  dataWS.appendRow(fieldValues)
  idCell.setValue(nextID)
  nextIDCell.setValue(nextID 1)
  ss.toast("New Record Created","ID:"   nextID)
}

Everything here works except the appendRow. To be completely honest, I'm not sure if I would be able to even understand the responses, so if you can, pretend that you're explaining to a 5 year old.

If you want the second code for the financial stuff to compare, then here it is:

const ss = SpreadsheetApp.getActiveSpreadsheet()

const forumWS = ss.getSheetByName("Forum")
const behindthescenesWS = ss.getSheetByName("BehindTheScenes")
const dataWS = ss.getSheetByName("Data")

const idCell = forumWS.getRange("C5")
const searchCell = forumWS.getRange("C3")

const fieldRange = ["F5","C7","C9","F7","F9"]
const data2WS = ss.getSheetByName("Data2")

const idCell2 = forumWS.getRange("N5")
const searchCell2 = forumWS.getRange("K3")

const fieldRange2 = ["K5","K7","K9","N9"]
function SaveTransaction() {

  const id = idCell2.getValue()

  if(id == ""){
    CreateNewTransaction()
    return
  }

  const cellFound = data2WS.getRange("A:A")
                      .createTextFinder(id)
                      .matchCase(true)
                      .matchEntireCell(true)
                      .findNext()

  if(!cellFound) return
  const row = cellFound.getRow()
  const fieldValues = fieldRange2.map(f => forumWS.getRange(f).getValue())
  fieldValues.unshift(id)
  data2WS.getRange(row,1,1,fieldValues.length).setValues([fieldValues])
  searchCell2.clearContent()
ss.toast("The current record of the transaction has been edited!","ID: "   id)
}

function CreateNewTransaction() {
  const fieldValues = fieldRange2.map(f => forumWS.getRange(f).getValue())

  const nextIDCell = behindthescenesWS.getRange("D3")
  const nextID = nextIDCell.getValue()
  fieldValues.unshift(nextID)

  //console.log(fieldValues)
  data2WS.appendRow(fieldValues)
  idCell2.setValue(nextID)
  nextIDCell.setValue(nextID 1)
  ss.toast("A new transaction has been recorded!","ID: "  nextID)
}

Here is also a picture of what the blank row looks like. I have no clue why it enters it twice. Empty rows below filled rows from when it was working

CodePudding user response：

As a guess... In the definition of the function createNewRecord() try to change the line:

const fieldValues = fieldRange.map(f => forumWS.getRange(f).getValue())

to:

const fieldValues = fieldRange.map(f => behindthescenesWS.getRange(f).getValue())

CodePudding user response：

Try this:

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const fsh = ss.getSheetByName("Forum")
  const bsh = ss.getSheetByName("BehindTheScenes")
  const dsh = ss.getSheetByName("Data")
  const idCell = fsh.getRange("C5")
  const searchCell = fsh.getRange("C3")
  const fieldRange = ["F5", "C7", "C9", "F7", "F9"]
  const id = idCell.getValue()
  if (id == "") {
    const fieldValues = fieldRange.map(f => fsh.getRange(f).getValue())
    const nextIDCell = bsh.getRange("B3")
    const nextID = nextIDCell.getValue()
    fieldValues.unshift(nextID)
    dsh.appendRow(fieldValues)
    idCell.setValue(nextID)
    nextIDCell.setValue(nextID   1)
    ss.toast("New Record Created", "ID:"   nextID)
    return;
  }
  const cellFound = dsh.getRange("A:A"   dsh.getLastRow()).createTextFinder(id).matchCase(true).matchEntireCell(true).findNext()
  if (!cellFound) return;
  const row = cellFound.getRow()
  const fieldValues = fieldRange.map(f => fsh.getRange(f).getValue())
  fieldValues.unshift(id)
  dsh.getRange(row, 1, 1, fieldValues.length).setValues([fieldValues])
  searchCell.clearContent()
  ss.toast("Changes Saved!", "ID:"   id)
}