I made this code (index.js):
const { GoogleSpreadsheet } = require('google-spreadsheet')
const credentials = require('./credentials.json')
const { promisify } = require('util')
const docId = '1lrGt8C9gjrabhVjGYoFh-q40rLuRHz6u9vRZqVeLG-c'
const accessSheet = async() => {
const doc = new GoogleSpreadsheet(docId)
await promisify(doc.useServiceAccountAuth)(credentials)
const info = await promisify(doc.getInfo)()
const worksheet = info.worksheets[0]
const rows = await promisify(worksheet.getRows)({
})
rows.forEach(row => {
console.log(row.Title)
})
}
accessSheet()
I'm trying to connect my code with a google spreadsheet. For the connection, I'm using node.js, but I'm getting this error message.
C:\Users\rodrigo.pasini\Desktop\robo_grpr\node_modules\google-spreadsheet\lib\GoogleSpreadsheet.js:101
this.jwtClient = new JWT({
^
TypeError: Cannot set properties of undefined (setting 'jwtClient')
at useServiceAccountAuth (C:\Users\rodrigo.pasini\Desktop\robo_grpr\node_modules\google-spreadsheet\lib\GoogleSpreadsheet.js:101:20)
at node:internal/util:360:7
at new Promise (<anonymous>)
at useServiceAccountAuth (node:internal/util:346:12)
at accessSheet (C:\Users\rodrigo.pasini\Desktop\robo_grpr\index.js:9:47)
at Object.<anonymous> (C:\Users\rodrigo.pasini\Desktop\robo_grpr\index.js:19:1)
at Module._compile (node:internal/modules/cjs/loader:1101:14)
at Object.Module._extensions..js (node:internal/modules/cjs/loader:1153:10)
at Module.load (node:internal/modules/cjs/loader:981:32)
at Function.Module._load (node:internal/modules/cjs/loader:822:12)
Does anyone know why I'm getting this error? What am I doing wrong?
CodePudding user response：
When I saw your showing script and your error message, I'm worried that the current version of google-spreadsheet you are using might be different from your showing script. So, in this answer, I would like to propose a sample script converted from your showing script using the latest version of google-spreadsheet.
When I check the latest version of google-spreadsheet, it seems that it's 3.2.0. So, first, please check the version of google-spreadsheet you are using. And, please install the latest one.
When your script is modified for using with the latest one (v3.2.0), it becomes as follows.
Modified script:
const { GoogleSpreadsheet } = require("google-spreadsheet");
const creds = require('./credentials.json');
const doc = new GoogleSpreadsheet('###'); // Please set your Spreadsheet ID.
const accessSheet = async () => {
await doc.useServiceAccountAuth(creds);
await doc.loadInfo();
const worksheet = doc.sheetsByIndex[0];
const rows = await worksheet.getRows();
rows.forEach((row) => {
console.log(row.Title);
});
};
accessSheet();
Reference:
CodePudding user response：
@Tanaike, After using your suggest, I'm getting the following error message (I can't show the entire error message.)
response: {
status: 501,
statusText: 'Not Implemented',
headers: {
server: 'squid/3.4.8',
'mime-version': '1.0',
date: 'Tue, 03 May 2022 11:36:54 GMT',
'content-type': 'text/html',
'content-length': '0',
'x-squid-error': 'ERR_UNSUP_REQ 0',
'x-cache': 'MISS from spge00007',
'x-cache-lookup': 'NONE from spge00007:8080',
via: '1.1 spge00007 (squid/3.4.8)',
connection: 'close'
},
config: {
url: '/',
method: 'get',
headers: {
Accept: 'application/json, text/plain, */*',
Authorization: 'Bearer ya29.c.b0AXv0zTN9BXxNWEMoPnX8VUx2-YS25ktuLDKYamPZ63oV7WQPaDwETBR1fvnRVVvO0dgB1bvymnQTQQ6LsXSPet1uysnZ7jo3PJqh2kvJOnjjSbRbdjc-qMeJtown3KC2zIuBVw3r6EPIypaX6lUkFEYH082bVAU9qARPDWqPzziZymCWvG3708jSjceAcJTs1eH9sIxdLw1XQ59A3usQ_90trhhEpbw........................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................',
'User-Agent': 'axios/0.21.4',
host: 'sheets.googleapis.com',
'Proxy-Authorization': 'Basic cm9kcmlnby5wYXNpbmk6Um9kcmlnbzIyMTI='
},
params: {},
baseURL: 'https://sheets.googleapis.com/v4/spreadsheets/1lrGt8C9gjrabhVjGYoFh-q40rLuRHz6u9vRZqVeLG-c',
transformRequest: [ [Function: transformRequest] ],
transformResponse: [ [Function: transformResponse] ],
paramsSerializer: [Function: paramsSerializer],
timeout: 0,
adapter: [Function: httpAdapter],
xsrfCookieName: 'XSRF-TOKEN',
xsrfHeaderName: 'X-XSRF-TOKEN',
maxContentLength: -1,
maxBodyLength: -1,
validateStatus: [Function: validateStatus],
transitional: {
silentJSONParsing: true,
forcedJSONParsing: true,
clarifyTimeoutError: false
},
data: undefined
},
request: <ref *1> ClientRequest {
_events: [Object: null prototype] {
abort: [Function (anonymous)],
aborted: [Function (anonymous)],
connect: [Function (anonymous)],
error: [Function (anonymous)],
socket: [Function (anonymous)],
timeout: [Function (anonymous)],
prefinish: [Function: requestOnPrefinish]
},
_eventsCount: 7,
_maxListeners: undefined,
outputData: [],
outputSize: 0,
writable: true,
destroyed: false,
_last: true,
chunkedEncoding: false,
shouldKeepAlive: false,
maxRequestsOnConnectionReached: false,
_defaultKeepAlive: true,
useChunkedEncodingByDefault: false,
sendDate: false,
_removedConnection: false,
_removedContLen: false,
_removedTE: false,
_contentLength: 0,
_hasBody: true,
_trailer: '',
finished: true,
_headerSent: true,
_closed: false,
socket: <ref *2> Socket {
connecting: false,
_hadError: false,
_parent: null,
_host: 'proxypge01.pge.parana',
_readableState: ReadableState {
objectMode: false,
highWaterMark: 16384,
buffer: BufferList { head: null, tail: null, length: 0 },
length: 0,
pipes: [],
flowing: true,
ended: false,
endEmitted: false,
reading: true,
constructed: true,
sync: false,
needReadable: true,
emittedReadable: false,
readableListening: false,
resumeScheduled: false,
errorEmitted: false,
emitClose: false,
autoDestroy: true,
destroyed: false,
errored: null,
closed: false,
closeEmitted: false,
defaultEncoding: 'utf8',
awaitDrainWriters: null,
multiAwaitDrain: false,
readingMore: false,
dataEmitted: true,
decoder: null,
encoding: null,
[Symbol(kPaused)]: false
},
_events: [Object: null prototype] {
end: [Function: onReadableStreamEnd],
free: [Function: onFree],
close: [ [Function: onClose], [Function: socketCloseListener] ],
timeout: [Function: onTimeout],
agentRemove: [Function: onRemove],
error: [Function: socketErrorListener],
finish: [Function: bound onceWrapper] {
listener: [Function: destroy]
}
},
_eventsCount: 7,
_maxListeners: undefined,
_writableState: WritableState {
objectMode: false,
highWaterMark: 16384,
finalCalled: true,
needDrain: false,
ending: true,
ended: true,
finished: false,
destroyed: false,
decodeStrings: false,
defaultEncoding: 'utf8',
length: 0,
writing: false,
corked: 0,
sync: false,
bufferProcessing: false,
onwrite: [Function: bound onwrite],
writecb: null,
writelen: 0,
afterWriteTickInfo: null,
buffered: [],
bufferedIndex: 0,
allBuffers: true,
allNoop: true,
pendingcb: 1,
constructed: true,
prefinished: false,
errorEmitted: false,
emitClose: false,
autoDestroy: true,
errored: null,
closed: false,
closeEmitted: false,
[Symbol(kOnFinished)]: []
},
allowHalfOpen: false,
_sockname: null,
_pendingData: null,
_pendingEncoding: '',
server: null,
_server: null,
parser: null,
_httpMessage: [Circular *1],
[Symbol(async_id_symbol)]: 40,
[Symbol(kHandle)]: TCP {
reading: true,
onconnection: null,
[Symbol(owner_symbol)]: [Circular *2]
},
[Symbol(kSetNoDelay)]: false,
[Symbol(lastWriteQueueSize)]: 0,
[Symbol(timeout)]: null,
[Symbol(kBuffer)]: null,
[Symbol(kBufferCb)]: null,
[Symbol(kBufferGen)]: null,
[Symbol(kCapture)]: false,
[Symbol(kBytesRead)]: 0,
[Symbol(kBytesWritten)]: 0,
[Symbol(RequestTimeout)]: undefined
},
_header: 'GET https://sheets.googleapis.com/v4/spreadsheets/1lrGt8C9gjrabhVjGYoFh-q40rLuRHz6u9vRZqVeLG-c/ HTTP/1.1\r\n'
'Accept: application/json, text/plain, */*\r\n'
'Authorization: Bearer ya29.c.b0AXv0zTN9BXxNWEMoPnX8VUx2-YS25ktuLDKYamPZ63oV7WQPaDwETBR1fvnRVVvO0dgB1bvymnQTQQ6LsXSPet1uysnZ7jo3PJqh2kvJOnjjSbRbdjc-qMeJtown3KC2zIuBVw3r6EPIypaX6lUkFEYH082bVAU9qARPDWqPzziZymCWvG3708jSjceAcJTs1eH9sIxdLw1XQ59A3usQ_90trhhEpbw........................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................\r\n'
'User-Agent: axios/0.21.4\r\n'
'host: sheets.googleapis.com\r\n'
'Proxy-Authorization: Basic cm9kcmlnby5wYXNpbmk6Um9kcmlnbzIyMTI=\r\n'
'Connection: close\r\n'
'\r\n',
_keepAliveTimeout: 0,
_onPendingData: [Function: nop],
agent: Agent {
_events: [Object: null prototype] {
free: [Function (anonymous)],
newListener: [Function: maybeEnableKeylog]
},
_eventsCount: 2,
_maxListeners: undefined,
defaultPort: 80,
protocol: 'http:',
options: [Object: null prototype] { path: null },
requests: [Object: null prototype] {},
sockets: [Object: null prototype] {
'proxypge01.pge.parana:8080:': [ [Socket] ]
},
freeSockets: [Object: null prototype] {},
keepAliveMsecs: 1000,
keepAlive: false,
maxSockets: Infinity,
maxFreeSockets: 256,
scheduling: 'lifo',
maxTotalSockets: Infinity,
totalSocketCount: 1,
[Symbol(kCapture)]: false
},
socketPath: undefined,
method: 'GET',
maxHeaderSize: undefined,
insecureHTTPParser: undefined,
path: 'https://sheets.googleapis.com/v4/spreadsheets/1lrGt8C9gjrabhVjGYoFh-q40rLuRHz6u9vRZqVeLG-c/',
_ended: true,
res: IncomingMessage {
_readableState: ReadableState {
objectMode: false,
highWaterMark: 16384,
buffer: BufferList { head: null, tail: null, length: 0 },
length: 0,
pipes: [],
flowing: true,
ended: true,
endEmitted: true,
reading: false,
constructed: true,
sync: true,
needReadable: false,
emittedReadable: false,
readableListening: false,
resumeScheduled: false,
errorEmitted: false,
emitClose: true,
autoDestroy: true,
destroyed: true,
errored: null,
closed: true,
closeEmitted: true,
defaultEncoding: 'utf8',
awaitDrainWriters: null,
multiAwaitDrain: false,
readingMore: true,
dataEmitted: false,
decoder: null,
encoding: null,
[Symbol(kPaused)]: false
},
_events: [Object: null prototype] {
end: [ [Function: responseOnEnd], [Function: handleStreamEnd] ],
data: [Function: handleStreamData],
error: [Function: handleStreamError]
},
_eventsCount: 3,
_maxListeners: undefined,
socket: <ref *2> Socket {
connecting: false,
_hadError: false,
_parent: null,
_host: 'proxypge01.pge.parana',
_readableState: ReadableState {
objectMode: false,
highWaterMark: 16384,
buffer: [BufferList],
length: 0,
pipes: [],
flowing: true,
ended: false,
endEmitted: false,
reading: true,
constructed: true,
sync: false,
needReadable: true,
emittedReadable: false,
readableListening: false,
resumeScheduled: false,
errorEmitted: false,
emitClose: false,
autoDestroy: true,
destroyed: false,
errored: null,
closed: false,
closeEmitted: false,
defaultEncoding: 'utf8',
awaitDrainWriters: null,
multiAwaitDrain: false,
readingMore: false,
dataEmitted: true,
decoder: null,
encoding: null,
[Symbol(kPaused)]: false
},
_events: [Object: null prototype] {
end: [Function: onReadableStreamEnd],
free: [Function: onFree],
close: [Array],
timeout: [Function: onTimeout],
agentRemove: [Function: onRemove],
error: [Function: socketErrorListener],
finish: [Function]
},
_eventsCount: 7,
_maxListeners: undefined,
_writableState: WritableState {
objectMode: false,
highWaterMark: 16384,
finalCalled: true,
needDrain: false,
ending: true,
ended: true,
finished: false,
destroyed: false,
decodeStrings: false,
defaultEncoding: 'utf8',
length: 0,
writing: false,
corked: 0,
sync: false,
bufferProcessing: false,
onwrite: [Function: bound onwrite],
writecb: null,
writelen: 0,
afterWriteTickInfo: null,
buffered: [],
bufferedIndex: 0,
allBuffers: true,
allNoop: true,
pendingcb: 1,
constructed: true,
prefinished: false,
errorEmitted: false,
emitClose: false,
autoDestroy: true,
errored: null,
closed: false,
closeEmitted: false,
[Symbol(kOnFinished)]: []
},
allowHalfOpen: false,
_sockname: null,
_pendingData: null,
_pendingEncoding: '',
server: null,
_server: null,
parser: null,
_httpMessage: [Circular *1],
[Symbol(async_id_symbol)]: 40,
[Symbol(kHandle)]: TCP {
reading: true,
onconnection: null,
[Symbol(owner_symbol)]: [Circular *2]
},
[Symbol(kSetNoDelay)]: false,
[Symbol(lastWriteQueueSize)]: 0,
[Symbol(timeout)]: null,
[Symbol(kBuffer)]: null,
[Symbol(kBufferCb)]: null,
[Symbol(kBufferGen)]: null,
[Symbol(kCapture)]: false,
[Symbol(kBytesRead)]: 0,
[Symbol(kBytesWritten)]: 0,
[Symbol(RequestTimeout)]: undefined
},
httpVersionMajor: 1,
httpVersionMinor: 1,
httpVersion: '1.1',
complete: true,
rawHeaders: [
'Server',
'squid/3.4.8',
'Mime-Version',
'1.0',
'Date',
'Tue, 03 May 2022 11:36:54 GMT',
'Content-Type',
'text/html',
'Content-Length',
'0',
'X-Squid-Error',
'ERR_UNSUP_REQ 0',
'X-Cache',
'MISS from spge00007',
'X-Cache-Lookup',
'NONE from spge00007:8080',
'Via',
'1.1 spge00007 (squid/3.4.8)',
'Connection',
'close'
],
rawTrailers: [],
aborted: false,
upgrade: false,
url: '',
method: null,
statusCode: 501,
statusMessage: 'Not Implemented',
client: <ref *2> Socket {
connecting: false,
_hadError: false,
_parent: null,
_host: 'proxypge01.pge.parana',
_readableState: ReadableState {
objectMode: false,
highWaterMark: 16384,
buffer: [BufferList],
length: 0,
pipes: [],
flowing: true,
ended: false,
endEmitted: false,
reading: true,
constructed: true,
sync: false,
needReadable: true,
emittedReadable: false,
readableListening: false,
resumeScheduled: false,
errorEmitted: false,
emitClose: false,
autoDestroy: true,
destroyed: false,
errored: null,
closed: false,
closeEmitted: false,
defaultEncoding: 'utf8',
awaitDrainWriters: null,
multiAwaitDrain: false,
readingMore: false,
dataEmitted: true,
decoder: null,
encoding: null,
[Symbol(kPaused)]: false
},
_events: [Object: null prototype] {
end: [Function: onReadableStreamEnd],
free: [Function: onFree],
close: [Array],
timeout: [Function: onTimeout],
agentRemove: [Function: onRemove],
error: [Function: socketErrorListener],
finish: [Function]
},
_eventsCount: 7,
_maxListeners: undefined,
_writableState: WritableState {
objectMode: false,
highWaterMark: 16384,
finalCalled: true,
needDrain: false,
ending: true,
ended: true,
finished: false,
destroyed: false,
decodeStrings: false,
defaultEncoding: 'utf8',
length: 0,
writing: false,
corked: 0,
sync: false,
bufferProcessing: false,
onwrite: [Function: bound onwrite],
writecb: null,
writelen: 0,
afterWriteTickInfo: null,
buffered: [],
bufferedIndex: 0,
allBuffers: true,
allNoop: true,
pendingcb: 1,
constructed: true,
prefinished: false,
errorEmitted: false,
emitClose: false,
autoDestroy: true,
errored: null,
closed: false,
closeEmitted: false,
[Symbol(kOnFinished)]: []
},
allowHalfOpen: false,
_sockname: null,
_pendingData: null,
_pendingEncoding: '',
server: null,
_server: null,
parser: null,
_httpMessage: [Circular *1],
[Symbol(async_id_symbol)]: 40,
[Symbol(kHandle)]: TCP {
reading: true,
onconnection: null,
[Symbol(owner_symbol)]: [Circular *2]
},
[Symbol(kSetNoDelay)]: false,
[Symbol(lastWriteQueueSize)]: 0,
[Symbol(timeout)]: null,
[Symbol(kBuffer)]: null,
[Symbol(kBufferCb)]: null,
[Symbol(kBufferGen)]: null,
[Symbol(kCapture)]: false,
[Symbol(kBytesRead)]: 0,
[Symbol(kBytesWritten)]: 0,
[Symbol(RequestTimeout)]: undefined
},
_consuming: false,
_dumped: false,
req: [Circular *1],
responseUrl: 'http://proxypge01.pge.parana:8080/https://sheets.googleapis.com/v4/spreadsheets/1lrGt8C9gjrabhVjGYoFh-q40rLuRHz6u9vRZqVeLG-c/',
redirects: [],
[Symbol(kCapture)]: false,
[Symbol(kHeaders)]: {
server: 'squid/3.4.8',
'mime-version': '1.0',
date: 'Tue, 03 May 2022 11:36:54 GMT',
'content-type': 'text/html',
'content-length': '0',
'x-squid-error': 'ERR_UNSUP_REQ 0',
'x-cache': 'MISS from spge00007',
'x-cache-lookup': 'NONE from spge00007:8080',
via: '1.1 spge00007 (squid/3.4.8)',
connection: 'close'
},
[Symbol(kHeadersCount)]: 20,
[Symbol(kTrailers)]: null,
[Symbol(kTrailersCount)]: 0,
[Symbol(RequestTimeout)]: undefined
},
aborted: false,
timeoutCb: null,
upgradeOrConnect: false,
parser: null,
maxHeadersCount: null,
reusedSocket: false,
host: 'proxypge01.pge.parana',
protocol: 'http:',
_redirectable: Writable {
_writableState: WritableState {
objectMode: false,
highWaterMark: 16384,
finalCalled: false,
needDrain: false,
ending: false,
ended: false,
finished: false,
destroyed: false,
decodeStrings: true,
defaultEncoding: 'utf8',
length: 0,
writing: false,
corked: 0,
sync: true,
bufferProcessing: false,
onwrite: [Function: bound onwrite],
writecb: null,
writelen: 0,
afterWriteTickInfo: null,
buffered: [],
bufferedIndex: 0,
allBuffers: true,
allNoop: true,
pendingcb: 0,
constructed: true,
prefinished: false,
errorEmitted: false,
emitClose: true,
autoDestroy: true,
errored: null,
closed: false,
closeEmitted: false,
[Symbol(kOnFinished)]: []
},
_events: [Object: null prototype] {
response: [Function: handleResponse],
error: [Function: handleRequestError]
},
_eventsCount: 2,
_maxListeners: undefined,
_options: {
maxRedirects: 21,
maxBodyLength: 10485760,
protocol: 'http:',
path: 'https://sheets.googleapis.com/v4/spreadsheets/1lrGt8C9gjrabhVjGYoFh-q40rLuRHz6u9vRZqVeLG-c/',
method: 'GET',
headers: {
Accept: 'application/json, text/plain, */*',
Authorization: 'Bearer ya29.c.b0AXv0zTN9BXxNWEMoPnX8VUx2-YS25ktuLDKYamPZ63oV7WQPaDwETBR1fvnRVVvO0dgB1bvymnQTQQ6LsXSPet1uysnZ7jo3PJqh2kvJOnjjSbRbdjc-qMeJtown3KC2zIuBVw3r6EPIypaX6lUkFEYH082bVAU9qARPDWqPzziZymCWvG3708jSjceAcJTs1eH9sIxdLw1XQ59A3usQ_90trhhEpbw........................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................',
'User-Agent': 'axios/0.21.4',
host: 'sheets.googleapis.com',
'Proxy-Authorization': 'Basic cm9kcmlnby5wYXNpbmk6Um9kcmlnbzIyMTI='
},
agent: undefined,
agents: { http: undefined, https: undefined },
auth: undefined,
hostname: 'proxypge01.pge.parana',
port: '8080',
beforeRedirect: [Function: beforeRedirect],
nativeProtocols: { 'http:': [Object], 'https:': [Object] },
pathname: 'https://sheets.googleapis.com/v4/spreadsheets/1lrGt8C9gjrabhVjGYoFh-q40rLuRHz6u9vRZqVeLG-c/'
},
_ended: true,
_ending: true,
_redirectCount: 0,
_redirects: [],
_requestBodyLength: 0,
_requestBodyBuffers: [],
_onNativeResponse: [Function (anonymous)],
_currentRequest: [Circular *1],
_currentUrl: 'http://proxypge01.pge.parana:8080/https://sheets.googleapis.com/v4/spreadsheets/1lrGt8C9gjrabhVjGYoFh-q40rLuRHz6u9vRZqVeLG-c/',
[Symbol(kCapture)]: false
},
[Symbol(kCapture)]: false,
[Symbol(kNeedDrain)]: false,
[Symbol(corked)]: 0,
[Symbol(kOutHeaders)]: [Object: null prototype] {
accept: [ 'Accept', 'application/json, text/plain, */*' ],
authorization: [
'Authorization',
'Bearer ya29.c.b0AXv0zTN9BXxNWEMoPnX8VUx2-YS25ktuLDKYamPZ63oV7WQPaDwETBR1fvnRVVvO0dgB1bvymnQTQQ6LsXSPet1uysnZ7jo3PJqh2kvJOnjjSbRbdjc-qMeJtown3KC2zIuBVw3r6EPIypaX6lUkFEYH082bVAU9qARPDWqPzziZymCWvG3708jSjceAcJTs1eH9sIxdLw1XQ59A3usQ_90trhhEpbw........................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................'
],
'user-agent': [ 'User-Agent', 'axios/0.21.4' ],
host: [ 'host', 'sheets.googleapis.com' ],
'proxy-authorization': [
'Proxy-Authorization',
'Basic cm9kcmlnby5wYXNpbmk6Um9kcmlnbzIyMTI='
]
}
},
data: ''
},
isAxiosError: true,
toJSON: [Function: toJSON]
}