Going from a google spreadsheet to a JS array

Question

I have a google spreadsheet which i've made public and all and want to go from that to a JS array. I've been playing with it and trying to convert it into a JSON then into an array but i'm having no luck. Is there a way to go from my spreadsheet to a 2d JS array which I can then run functions on.

So if the spreadsheet looks like this:

NAME  COLOR  SIZE
MARK   BLUE     6
DAVE    RED     8

The array will be a 2d array such that value [0][0] will be MARK, [1][0] will be BLUE and [0][1] will be DAVE etc.

Any help would be much appreciated

Answer 1

Edit: since google API4 is our, and the module was updated accordingly, and since API3 will be discontinued on September, I updated the code accordingly.

I know this is old now, but in case any one else searching for an answer:

I played around with manipulating google spreadsheet in JS, and wanted to go from a worksheet with several sheets, to an array I can easily use in other functions.

So I wrote a program to access a Worksheet, get all of the sheets (tab) from within, reduce the all-empty rows and columns, and return the a new worksheet array. Each element in the worksheet array represents the sheet from the Excel worksheet. Also, each element has a 2d array of its cells, as such:

cellsArr:[[row],[col]]

To run the program, simply replace YOUR_CLIENT_SECRET.json to your client secret json file, and ENTER_GOOGLE_SPREADSHEET_ID_FROM_URL to the ID of the spreadsheet from the URL. For example:

docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=01

and then run:

getWorksheet(ENTER_GOOGLE_SPREADSHEET_ID_FROM_URL);

This returns a promise, which is resolved into the revised worksheet (as described above). Please note, reduceRowsCols() deletes empty rows and columns from each sheet. It deletes each row where the first column cell is empty, and every column where the first row cell is empty, so every row where [i][0]==null and every column where [0][i]==null. Simply remove reduceRowsCols() from the code to negate that

  

  const fs = require('fs');

const {GoogleSpreadsheet} = require('google-spreadsheet'),
    creds = require('./client_secret.json'), 

/**
 * @Description Authenticate with the Google Spreadsheets API using credentials json. 
 * @param {string} spreadsheet_id  - spreadsheet id to get. Get this from the URL of the spreadsheet
 * Returns the entire raw worksheet.
 * */
async function accessSpreadsheet(spreadsheet_id) {
    const doc = new GoogleSpreadsheet(spreadsheet_id);
    await doc.useServiceAccountAuth(creds);
    await doc.loadInfo();
    console.log(`${getCurrentTime()}: Loaded raw doc ${doc.title}`);
    return doc;
}
/**
 * @Description This is an async function to retrieve the raw worksheet
 * @param {string} spreadsheet_id  - spreadsheet id to get. Get this from the URL of the spreadsheet
 * */
async function getWorksheet(spreadsheet_id) {
    try {
        let res = await accessSpreadsheet(spreadsheet_id);
        console.log(`${getCurrentTime()}: Organizing sheets for extraction...`);
        res = await getCondensedWorksheets(res.sheetsByIndex);
        createSheetsCopy(res);
        return res;
    } catch (e) {
        throw (e);
    }
}

/**
 * @param worksheetsArr
 */
async function getCondensedWorksheets(worksheetsArr) {
    if (!Array.isArray(worksheetsArr)) {
        throw `getCondensedWorksheets: worksheets variable passed is not an array. Object passed:\n${worksheetsArr}`;
    }
    let revisedArr = [];
        for (let i = 0; i < worksheetsArr.length; i++) {
        // for (let i = 0; i < 1; i++) {
        
        revisedArr.push(await worksheetsArr[i].getRows().then((res)=>{
            let thisRevised = {
                id: worksheetsArr[i]._rawProperties.sheetId,
                title: worksheetsArr[i].title,
                rowCount: worksheetsArr[i].rowCount,
                colCount: worksheetsArr[i].columnCount,
                getRows: worksheetsArr[i].getRows,
                getHeaders: worksheetsArr[i].headerValues,
                getCells    :   res.map( row => {return(row._rawData);}),
                resize: worksheetsArr[i].resize,
            }                
            return getCells2dArray(thisRevised);
        }))
    }
    return Promise.all(revisedArr).then(()=>{
        return revisedArr;
    })    
}

/**
 * @param {array} thisSheet - a single sheet (tab)
 */
function getCells2dArray(thisSheet) {
        let sheetCellsArr = [];
       
        sheetCellsArr.push(thisSheet.getHeaders);
        thisSheet.getCells.map(row => {
            sheetCellsArr.push(row);
        })
        thisSheet.cellsArr = sheetCellsArr;   
        delete thisSheet.getCells;
        reduceRowsCols(thisSheet); 
    return thisSheet;
}

function reduceRowsCols(thisSheet) {
    for (let i = 0; i < thisSheet.cellsArr.length; i++) {
        if (thisSheet.cellsArr[i][0] == null) {
            thisSheet.cellsArr.slice(0, i);
            break;
        }
    }

    for (let i = 0; i < thisSheet.cellsArr[0].length; i++) {
        if (thisSheet.cellsArr[0][i] == null) {
            thisSheet.cellsArr[0].slice(0, i);
            break;
        }
    }
}

const getCurrentTime = function (){
    var date = new Date();
    return `${date.getHours()}:${date.getMinutes()}:${date.getSeconds()}:${date.getMilliseconds()}`
}

module.exports = {getWorksheet};

Answer 2

There are two methods in GAS that allow for reading values in cells. They are .getValue() and .getValues(). As it is implied, .getValue() retrieves only the value of the top left cell in a range. .getValues() retrieves a 2D array of all values in your range.

The code below defines the variables necessary to read the range that follows. The range is defined (based off your example) as starting at the second row (ignores your header row), continuing for 3 columns and down to the last row with content.

You'll notice that we find .getLastRow() but then define the number of rows to read as lastRow - 1. This is because .getLastRow() gives us the position of the last row with data; or more simply, an integer of how many rows have data. If we have 20 rows with data, that includes the header row which we would like to ignore. Reading 20 rows after shifting our range down a row will include the first empty row. Reading 19 rows instead stops after reading the last data-filled row.

Note that .getRange() requires the input of .getRange(startRow, startColumn, numRows, numColumns). This can be found with more detail and explanation in the GAS Reference sheet here

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSpreadsheet();
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(2, 1, (lastRow - 1), 3);
  var dataArray = range.getValues();
}

Values in this array can then be called, defined, read, etc. as you would expect for Javascript. You will find in the GAS Reference that many methods have a singular and plural version such as .getBackground() and .getBackgrounds(). These will act in the same comparative way as .getValue() vs. .getValues().

It may also be helpful to note that even if your range is a singular row but many columns, .getValues() gives you a 2D array where all values will start with [0] as the only possible value for row is 0.