Google-api-nodejs-client: What is fields in sheets.spreadsheets.batchUpdate()?

Created on 7 Jul 2019  路  16Comments  路  Source: googleapis/google-api-nodejs-client

Hello!

I'm trying to update cells by calling sheets.spreadsheets.batchUpdate(), but can't understand how to specify range with address of cells? Is property fields intends for that and what values can it take for example?

import { google, sheets_v4 } from 'googleapis';
const sheets = google.sheets('v4');

sheets.spreadsheets.batchUpdate({
    auth: ...,
    spreadsheetId: ...,
    requestBody: {
        requests: [{
            appendCells: {
                sheetId: 0,
                fields: '???', // What is it?
                rows: [{
                    values: [{
                        userEnteredValue: {
                            stringValue: '...',
                        },
                    }],
                }],
            },
        }],
    },
});
sheets question

Most helpful comment

Actually I finally got it! Check it out :)

This is using google scripts by the way, nothing to do with your package! This sets 4 cells in a 2x2 grid, and turns one red! lmao

function andrews_colorizer() {
  var spreadsheetId = '1cMVpv12tgUe3kvK-vfZHflENtvBp53T_XaP2WWpUXx0';
  myFunction();
  var result = Sheets.Spreadsheets.batchUpdate({
    requests: [{
      updateCells: {
        rows: [{
          values: [{
            userEnteredValue: {
              stringValue: 'chicken'
            },
            userEnteredFormat: {
              backgroundColor: {
                red: 1,
                green: 0,
                blue: 0,
                alpha: 1
              }
            }
          }, {
            userEnteredValue: {
              stringValue: 'turkey'
            }
          }]
        }, {
          values: [{
            userEnteredValue: {
              stringValue: 'chimken'
            }
          }, {
            userEnteredValue: {
              stringValue: 'perky'
            }
          }]
        }],
        fields: 'userEnteredValue.stringValue,userEnteredFormat.backgroundColor',
        start: {
          sheetId: 0,
          rowIndex: 0,
          columnIndex: 0
        }
      }
    }]
  }, spreadsheetId)
  Logger.log(result);
}

All 16 comments

I haven't used this part of the API, but maybe @erickoledadevrel or @sqrrrl know how to do this bit :)

If you're updating existing cells, use updateCells instead. The appendRows operation doesn't take a range as it will always add to the end of the sheet.

The updateRows operation has a range property which you use to specify which cells to update.

As for the fields parameter, that is important when using updateCells. It's a list of the properties of the cell that are being updated in the request. For example, if you're only updating the value and want to leave the formatting alone, then you'd set fields to 'userEnteredValue'.

In other words, your request should look more like:

sheets.spreadsheets.batchUpdate({
    auth: ...,
    spreadsheetId: ...,
    requestBody: {
        requests: [{
            updateCells: {
                range: {
                    sheetId: 0,
                    startRowIndex: ...,
                    endRowIndex: ...,
                    startColumnIndex: ...,
                    endColumnIndex: ...,
                },
                fields: 'userEnteredValue'
                rows: [{
                    values: [{
                        userEnteredValue: {
                            stringValue: '...',
                        },
                    }],
                }],
            },
        }],
    },
});

@sqrrrl thank you, but I exactly need to append values. Sorry for not exact formulation. It seems that I need to additionally specify addBanding or addNamedRange next to appendCells for this?

Documentation said about fields property something other:
小薪懈屑芯泻 褝泻褉邪薪邪 2019-07-08 胁 21 15 16

Pity that API of sheets.spreadsheets.batchUpdate() so difficult, I decide to use separate request by call sheets.spreadsheets.values.append() for my purposes.

Man I've been a javascript developer for 6 years now. It's gotten to the point where almost nothing can stump me. I love learning new stuff.

Well so I thought, until today when my cousin asked me how to programatically change the background color of a cell. I thought, "Damn! I should be able to show off my stellar skills to my cousin!". Never have I been so wrong. What a wrangled documentation, lacking examples, leaving it all up to me on how to figure out how to change the color of a box.

It's taken me 3 hours to get this far and now I'm just stumped.

function andrews_colorizer() {
  var spreadsheetId = '1cMVpv12tgUe3kvK-vfZHflENtvBp53T_XaP2WWpUXx0';
  Sheets.Spreadsheets.batchUpdate({
    requests: [{
      updateCells: {
        rows: [{
          values: [{
            formattedValue: 'chicken'
          }]
        }],
        fields: 
      }
    }]
  }, spreadsheetId)
}

@Andrew1431 sorry for the frustration, I'll see if I can find someone to point at this question.

Actually I finally got it! Check it out :)

This is using google scripts by the way, nothing to do with your package! This sets 4 cells in a 2x2 grid, and turns one red! lmao

function andrews_colorizer() {
  var spreadsheetId = '1cMVpv12tgUe3kvK-vfZHflENtvBp53T_XaP2WWpUXx0';
  myFunction();
  var result = Sheets.Spreadsheets.batchUpdate({
    requests: [{
      updateCells: {
        rows: [{
          values: [{
            userEnteredValue: {
              stringValue: 'chicken'
            },
            userEnteredFormat: {
              backgroundColor: {
                red: 1,
                green: 0,
                blue: 0,
                alpha: 1
              }
            }
          }, {
            userEnteredValue: {
              stringValue: 'turkey'
            }
          }]
        }, {
          values: [{
            userEnteredValue: {
              stringValue: 'chimken'
            }
          }, {
            userEnteredValue: {
              stringValue: 'perky'
            }
          }]
        }],
        fields: 'userEnteredValue.stringValue,userEnteredFormat.backgroundColor',
        start: {
          sheetId: 0,
          rowIndex: 0,
          columnIndex: 0
        }
      }
    }]
  }, spreadsheetId)
  Logger.log(result);
}

Well, now that I finally understand how it's all working, I'm building a class that acts as a builder for this horrible syntax. Totally cleaned up my code!

function myFunction() {
  var builder = new Builder('1HnP8F8Gy2Brjh1OCF_DI_EPk6t7ACsnLAJX6kLbErgk')
    .setValuesFromOrigin('J7', 0, [1, 2, 3])
    .setValuesFromOrigin('J9', 0, ['hey', 'there'])
    .setValuesFromOrigin('J12', 0, ['hi', 'curtis'])
    .execute();
}

@Andrew1431 awesome, thank you for keeping this issue updated for the benefit of other folks.

@tenorok does @Andrew1431's approach work for you?

Whoa! @Andrew1431 is really wanted to change the color of that cell :D

I'm not understand destination of property fields, why we should pass it? Doesn't the data speak for itself?

Besides API of sheets.spreadsheets.values.append() is more friendly for me, because it not require transform sheet index to the name of sheet, row and column indexes to a cell name.

By the way, @Andrew1431 may be you can show sources of your builder?

Absolutely!

This script was built for running in the googlescripts so it may require some modifications for your environment.

If you're running it in your own node environment, you should be running a babel compiler atleast, for Class, object spread, Array.prototype.map, and other features that I may have forgotten.


/**
 * Turns A1 into coordinates [0, 0], C9 into [2, 8] etc..
 * which is how the batchUpdate method targets it's cells.
 * This was mainly for simplicity for my cousin so this
 * could always be removed.
 */
function getIndexFromR1Notation(R1) {
  var [_, letters, row] = R1.match(/([a-z]+)(\d+)/i);
  var column;
  if (letters.length === 1) {
    column = letters.charCodeAt(0) - 64 - 1;
  } else {
    column = letters.charCodeAt(1) - 64 + 26 - 1;
  }
  return {
    rowIndex: row - 1,
    columnIndex: column
  };
}

/**
 * Converts hex in to the
 * { red: 1, green: 1, blue: 1 }
 * object that batchUpdate requests
 * requre.
 */
function getRGBFromHex(hex) {
  var result = /^#?([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})$/i.exec(hex);
  return {
    red: parseInt(result[1], 16) / 255,
    blue: parseInt(result[2], 16) / 255,
    green: parseInt(result[3], 16) / 255
  };
}

/**
 * Instantiate a new builder variable and pass
 * in the spreadsheetId you are targetting.
 *
 * Ex.
 * var builder = new Builder('your_spreadsheet_id');
 * 
 * All methods return the instance of the builder,
 * which means that all these commands can be chained.
 * 
 * Ex.
 *   var builder = new Builder('1cMVpv12tgUe3kvK-vfZHflENtvBp53T_XaP2WWpUXx0')
 *    .setValuesFromOrigin('B3', 0, [1, 2, 3], [4, 5, 6])
 *    .setValuesFromOrigin('C9', 0, ['hey', 'there', 'how', 'are', 'you'])
 *   if (you_want_to_conditionally_apply_an_update) {
 *     builder.setValuesFromOrigin('C10', 0 ['Then just do it!'])
 *   }
 *   builder.execute();
 */
class Builder {
  constructor(id) {
    this.spreadsheetId = id;
    this.requests = [];
  }

  /**
   * Set the cell background colors.
   * 
   * var builder = new Builder(spreadsheetId);
   * builder.setBackgroundColorsFromOrigin('J10', 0, ['#ff0000', '#0000ff'], ['#00ff00'], ['#ffffff'])
   * builder.execute()
   *
   * This would start at origin J10, targeting sheetId 0,
   * and then apply any number of arguments where each separate
   * array is a new row.
   * 
   * Result of above example:
   * J10 = RED
   * J11 = BLUE
   * K10 = GREEN
   * L10 = WHITE
   */
  setBackgroundColorsFromOrigin(R1, sheetId, ...colors) {
    var rows = colors.map((value) => ({
      values: value.map(v => ({
        userEnteredFormat: {
          backgroundColor: {
            ...getRGBFromHex(v),
            alpha: 1
          }
        }
      }))
    }));
    this.requests.push({
      updateCells: {
        rows,
        fields: 'userEnteredFormat.backgroundColor',
        start: {
          sheetId,
          ...getIndexFromR1Notation(R1)
        }
      }
    });
    return this;
  }

  /**
   * Same signature as above except for setting
   * string values.
   * This was all my cousin needed so
   * you can just copy these functions
   * and form them to what you're looking
   * for as needed!
   */
  setValuesFromOrigin(R1, sheetId, ...values) {
    var rows = values.map((value) => ({
      values: value.map(v => ({
        userEnteredValue: {
          stringValue: v.toString()
        }
      }))
    }));
    this.requests.push({
      updateCells: {
        rows,
        fields: 'userEnteredValue.stringValue',
        start: {
          sheetId,
          ...getIndexFromR1Notation(R1)
        }
      }
    });
    return this;
  }

  /**
   * Finally, after you've built out the requests object
   * you can execute this.
   * This script is running in the googlescripts environment
   * so you may want to swap this out with how your server works
   * OR 
   * You can just access builder.requests object which is what the
   * API expects, it becomes rather larger after multiple calls!
   */
  execute() {
    return Sheets.Spreadsheets.batchUpdate({
      requests: this.requests
    }, this.spreadsheetId);
  }
}

@tenorok I agree, they should be able to just check what fields we're targetting and apply them, but alas the field is there so I might as well quickly explain:

It just acts as a mask so that you have peace of mind knowing that it's not going to affect other properties of a cell. If I understood correctly from their documentation, if you were to apply the field mask to a property that you didn't define, it would act as a RESET on that field. Hence the field mask!

@Andrew1431 How do you manage to get the OAuth 2.0 token? Like, in a code of a example we should use the prompt to manually input the token code that we get when the app gets the permission of our account.

Sorry this is running in googlescripts cloud tied to a sheet so there was no token management, it's all done automatically

Alright, thanks

Greetings folks! It looks like the original question here was answered :) If you're still having issues, please let us know!

Was this page helpful?
0 / 5 - 0 ratings

Related issues

ddimitrioglo picture ddimitrioglo  路  24Comments

ghost picture ghost  路  29Comments

bretthadley picture bretthadley  路  18Comments

yannbertrand picture yannbertrand  路  24Comments

tomaspinho picture tomaspinho  路  27Comments