JSON Formatting from Sheets v4 API

Ask the community
,
1

I’ve been able to successfully adapt the Google Sheets NPM example from here to write data to my spreadsheet as described. I can access the info in my sheet and authorize via API v4, but I can’t for the life of me figure out how to get the response from my GET request into an array that can be adapted into a repeater. When I try to parse the results with JSON.parse(), I get an unknown. When I log the raw values, I don’t get a usable array. I’d like to take the results and have the first item in the array be the keys for each additional item in the array being different values.

What I have:

{...}
range: "Sheet1!A1:C23"
majorDimension: "ROWS"
values: Array(5)
    0: Array(3)
        0: "First"
        1: "Last"
        2: "Status"
    1: Array(3)
        0: "Mike"
        1: "Jones"
        2: "Active"
    2: Array(3)
        0: "Bob"
        1: "Smith"
        2: "Active"
    3: Array(3)
        0: "Bill"
        1: "Jolly"
        2: "Inactive"
    4: Array(3)
        0: "Sam"
        1: "Snead"
        2: "Active"

What I think I need:

{...}
values: Array(4)
    0: Array(3)
        "First": "Mike"
        "Last": "Jones"
        "Status": "Active"
    1: Array(3)
        "First": "Bob"
        "Last": "Smith"
        "Status": "Active"
etc....

I’ve used the API v3 to extract the raw JSON with [$t] values before, but I can’t figure out how to take my results and format them into individual items inside an array with row 1 being the keys. I’d like to transition to API v4 to avoid deprecation and avoid having a public spreadsheet.

site: https://blake302.wixsite.com/mysite/blank

Back-end code

export async function main() {
 
 const sheetId = await getSheetId();
 
 const authClient =  await createAuthorizedClient();
 const request = {
 // The ID of the spreadsheet to retrieve data from.
    spreadsheetId: sheetId,  // TODO: Update placeholder value.

 // The A1 notation of the values to retrieve.
    range: 'A1:C23',  // TODO: Update placeholder value.

 // How values should be represented in the output.
 // The default render option is ValueRenderOption.FORMATTED_VALUE.
    valueRenderOption: 'FORMATTED_VALUE',  // TODO: Update placeholder value.

 // How dates, times, and durations should be represented in the output.
 // This is ignored if value_render_option is
 // FORMATTED_VALUE.
 // The default dateTime render option is [DateTimeRenderOption.SERIAL_NUMBER].
 //dateTimeRenderOption: 'FORMATTED_STRING',  // TODO: Update placeholder value.

    auth: authClient,
  };

 try {
 const response = (await sheets.spreadsheets.values.get(request)).data;
 // TODO: Change code below to process the `response` object:
    console.log(JSON.stringify(response, null, 2));
 var results = response.values
 await console.log(results)
 return results
  } catch (err) {
    console.error(err);
  }
}

Page Code

import { main } from 'backend/googleSheet'
import { isConfig } from 'backend/config'

$w.onReady(async function () {
 let config = await isConfig();

let results = await main()

//Can set text box to hard coded value in results in preview mode, but doesn't work in published site
$w("#text28").text = results[1][1];

await console.log("Answer is: " + await main())

$w("#repeater1").data = results;

$w("#repeater1").onItemReady(($w, itemData, index) => {

        $w("#text27").text = itemData[1][1];
        $w("#text16").text = itemData[2][1];
        $w("#text17").text = itemData[1][2];
}
);
});