modify google spreadsheet using Ajax javascript only

Question

i am using javascript only to read and write values in google spreadsheet (private sheet using oAuth). I can successfully read the values but i am getting issue on modifying the sheet. below is my code for updating the cell-

 $.ajax({
          type: 'post',
            headers: {
                Authorization: auth,
                'content-type': 'application/json',
                "access_token": auth,
            },
            data: JSON.stringify({ // generate from oAuthPlayground
                "access_token": auth,
                
                "range": "A1",
                "values": [
                    [
                        "32",
                        
                    ]
                ]
            
            
            }),              
            url: 'https://sheets.googleapis.com/v4/spreadsheets/' + sheetId + '/values/A2:batchUpdate?insertDataOption=INSERT_ROWS&valueInputOption=RAW',               
            success: function (r) {
                console.log(r)
            }, error: function (r) {
                console.log(r)
            }
        });

anybody can suggest what is the mistake. Again i am using only ajax not node.js or google script.

i am getting issue on modifying the sheet: which issue exactly? — sp00m
– sp00m, Commented Mar 30, 2022 at 16:58
Could you include a minimal example? The question is not clear enough to give a useful answer. — Gabriel Carballo
– Gabriel Carballo, Commented Mar 30, 2022 at 19:50

Tanaike · Accepted Answer · 2022-03-31 01:13:36Z

4

From your showing script, I couldn't understand the following.

What method in Sheets API do you want to use?
Which do you want to put the value to the cell "A1" or "A2".
I couldn't understand whether your access token can be used for updating the Spreadsheet.

From this situation, I guessed your current issue and your goal as follows.

You want to put a value of "32" to a cell "A1" of the 1st sheet of the Spreadsheet.
- In this case, "Method: spreadsheets.values.update" is used.
Your access token can be used for updating the Spreadsheet.

In this case, how about the following sample script?

Sample script:

const auth = "Bearer ###"; // Please replace "###" with your access token.
const sheetId = "###"; // Please set your Spreadsheet ID.

$.ajax({
  type: 'put',
  headers: { Authorization: auth, 'content-type': 'application/json' },
  data: JSON.stringify({
    "values": [["32"]]
  }),
  url: 'https://sheets.googleapis.com/v4/spreadsheets/' + sheetId + '/values/A1?valueInputOption=RAW',
  success: function (r) {
    console.log(r)
  }, error: function (r) {
    console.log(r)
  }
});

When this script is run, "32" is put to a cell "A1" of the 1st sheet of the Spreadsheet.

Reference:

Method: spreadsheets.values.update

answered Mar 31, 2022 at 1:13

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3369120 Over a year ago

how i can update multiple cells value at one time? suppose i want to update cells A5,C5 and F5 all together. i have done something lie this. but it doesn't work.******* type: 'put', headers: { Authorization: "Bearer " +auth, 'content-type': 'application/json' }, data: JSON.stringify({ "range": "Users!A5", "values": [["ABC"]] },{ "range": "Users!C5", "values": [["XYZ"]] }), url: 'sheets.googleapis.com/v4/spreadsheets/'+ sheetId + '?valueInputOption=RAW',

Tanaike Over a year ago

@user3369120 About your new question, I found your posted new question. So I posted an answer to it. Please confirm it.

Collectives™ on Stack Overflow

modify google spreadsheet using Ajax javascript only

1 Answer 1

Sample script:

Reference:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample script:

Reference:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related