Is there a way to pass a variable as a value in google sheets API?

Question

I am newer to programming (just a heads up, apologies in advance for my code being abysmal) I am building a tool for my employer to be able to basically upload a CSV file with marketing metrics related data, and it will update an external google sheet that we use to present marketing metrics to the client.

The issue I am having is in the value I am trying to send to the google sheet. Whenever I use a string, the data sends without issue, but when I upload my test CSV, the value logs to the console, but it will not pass to google sheets (it's just blank, nothing new on the sheet.)

I have also tried just passing a raw variable through, and I get the error: ""Invalid values[5][0]: list_value {}""

Is there a way I can send finalResult into google sheets?

I have tried the following things to sort this out:

1. I have tried converting the variable into a string (this is my most current setup presented below)
2. I have tried building out a new variable (finalReach) that is equal to DCJRtotalreach
3. I have tried using the original DCJRtotalreach variable as well

What I think I need to do is convert my variable into an array, with the finalResult variable as my only value in that string, and then maybe pass "arrayname[0]" in the values section? But it wouldn't make sense to me because you have an array within an array right? My brain is exploding because this is where my knowledge is more limited on this front.

I am thankful for any input/advice. Thanks so much!

//This is where papa parse parses the data, the reason for so many arrays is that there are multiple cells that need to be parsed and then added at the end to provide a total amount. 

const DCJRtotalreach = [];
const uploadConfirm = document.getElementById('submit').addEventListener('click', () => {
    Papa.parse(document.getElementById('magfile').files[0],
    {
        download: true,
        header: false,
        skipEmptyLines: true,
        complete: function(resultsa){
            var reach1Array = resultsa.data[2];
            var reach1 = reach1Array.slice(1, 2);
            var arrayParse1 = reach1.map((x) => parseInt(x));

            var reach2Array = resultsa.data[3];
            var reach2 = reach2Array.slice(1, 2);
            var arrayParse2 = reach2.map((x) => parseInt(x));

            var reach3Array = resultsa.data[4];
            var reach3 = reach3Array.slice(1, 2);
            var arrayParse3 = reach3.map((x) => parseInt(x));

            var reach4Array = resultsa.data[5];
            var reach4 = reach4Array.slice(1, 2);
            var arrayParse4 = reach4.map((x) => parseInt(x));
            
            var reach5Array = resultsa.data[6];
            var reach5 = reach5Array.slice(1, 2);
            var arrayParse5 = reach5.map((x) => parseInt(x));

            var reach6Array = resultsa.data[7];  
            var reach6 = reach6Array.slice(1, 2);
            var arrayParse6 = reach6.map((x) => parseInt(x));

            //This is where I am taking each cell's data and creating a sum value

            DCJRWHtotalReach = arrayParse1[0] + arrayParse2[0] + arrayParse3[0] + arrayParse4[0] + arrayParse5[0] + arrayParse6[0]
            console.log(String(DCJRWHtotalReach));
            const finalResult = DCJRtotalreach;

            // This is where I have set up my values to send this data to google sheets. 

    async function WHDCJRmetrics () {
      return response = await gapi.client.sheets.spreadsheets.values.update({
          "spreadsheetId": "THIS IS WHERE I KEEP MY SPREADSHEET ID",
          "range": "Sheet1!C6",
          "includeValuesInResponse": true,
          "responseDateTimeRenderOption": "FORMATTED_STRING",
          "responseValueRenderOption": "FORMATTED_VALUE",
          "valueInputOption": "RAW",
          "resource": {
           "values": [
           [
               String(finalResult) //This is meant to be passed as a string but is not functioning, however this DOES work if I put "hello world" for an example.
              ]
           ]
                  }
                      });

        }
   WHDCJRmetrics();
        }       
    });
});

Answer 1

When I saw your script, it seems that finalResult is DCJRtotalreach. And, DCJRtotalreach is declared as const DCJRtotalreach = [];. In your script, DCJRtotalreach is always empty. By this, "values": [[]] is used as the value. By this, an empty value is put. I thought that this might be the reason for your current issue. If you want to put the value of DCJRWHtotalReach to the cell "Sheet1!C6", how about the following modification?

From:

"valueInputOption": "RAW",
"resource": {
 "values": [
 [
     String(finalResult) //This is meant to be passed as a string but is not functioning, however this DOES work if I put "hello world" for an example.
    ]
 ]

To:

"valueInputOption": "USER_ENTERED",
"resource": { "values": [[DCJRWHtotalReach]] }

• By this modification, the number value of DCJRWHtotalReach is put into the cell "Sheet1!C6".

Note:

• This modification supposes that your gapi.client.sheets.spreadsheets.values.update works fine for putting to your Google Spreadsheet with Sheets API. Please be careful about this.

• When I tested the modified script of your script using a sample value, no error occurs, and I confirmed that a value of DCJRWHtotalReach could be put into the cell "Sheet1!C6". But, if an error occurs, please confirm your value of DCJRWHtotalReach again. And, if an error occurs at another part, please provide your sample value of resultsa for correctly replicating the issue. By this, I would like to confirm it.