How to write to a specific range of a sheet in goo

问题:

This is my first time using the google sheets API, and I'm trying to write a script that takes in some data and processes it, which works, and then I want to write the processed data to a column in an already existing sheet, which it what I need help with.

Is there some better way to put an array in a specific range?

The error message says cannot get range of null, this is my code so far, the line that isn't part of the block is the issue:

function getList() {
 var spreadsheetId = '1BjnZ-SPBVcCdITxcvEX1nCPecvGdYSKQ0m_GiaKduZQ';
 var rangeName = 'Paste Here!I1:GZ6';
 var pasteRange = 'The Goods!B3:B1000';
 var values = Sheets.Spreadsheets.Values.get(spreadsheetId, rangeName).values;
 var width = values[0].length;
 var teamNumbers = [];
 if (!values) {
   Logger.log('No data found.');
 } else {
   for(var col = 1; col < width; col+=2) {
     for(var row = 0; row < values.length; row++) {
       teamNumbers += values[row][col].toString();
       teamNumbers += '\n'
     }
    }

SpreadsheetApp.getActiveSpreadsheet().getRange(pasteRange).setValues(teamNumbers);

  }
}

Also, secondary question, how can you make the range for something a variable, as in in order to get I1:GZ6 from a parameter?

Any help is much appreciated, thanks in advance!

回答1:

The only reason you would get the error

Cannot call method getRange of null

from the line SpreadsheetApp.getActiveSpreadsheet() is if you are using a standalone script instead of a bound script. Standalone scripts must use SpreadsheetApp.openById("some id") in order to access a spreadsheet.

For more information on the differences, consult the Apps Script documentation

Standalone scripts
SpreadsheetApp#getActiveSpreadsheet

回答2:

For your 1st question

How about this modification?

Modification points :

• teamNumbers is declared as an array. But it is used as string.
  • In order to import values to spreadsheet using Sheets API, teamNumbers has to be used as the 2 dimensional array.
  • If you want to import the string value in one cell,
    • In the case of use of Sheets API, please use teamNumbers = [[value]].
    • In the case of use of setValue(teamNumbers) of SpreadsheetApp, please use teamNumbers = value.
• If you want to use SpreadsheetApp.getActiveSpreadsheet().getRange(pasteRange).setValues(teamNumbers), pasteRange is required to be matched to the size of rows and columns of teamNumbers.
• If you want to use var rangeName = 'Paste Here!I1:GZ6', you can use Sheets.Spreadsheets.Values.update() of Sheets API.
  • In this modification, this was used.

Modified script :

function getList() {
  var spreadsheetId = '1BjnZ-SPBVcCdITxcvEX1nCPecvGdYSKQ0m_GiaKduZQ';
  var rangeName = 'Paste Here!I1:GZ6';
  var pasteRange = 'The Goods!B3:B1000';
  var values = Sheets.Spreadsheets.Values.get(spreadsheetId, rangeName).values;
  var width = values[0].length;
  var teamNumbers = [];
  if (!values) {
    Logger.log('No data found.');
  } else {
    for(var col = 1; col < width; col+=2) {
      for(var row = 0; row < values.length; row++) {
        teamNumbers.push([values[row][col].toString()]); // Modified
      }
    }
    var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId(); // Modified
    Sheets.Spreadsheets.Values.update({values: teamNumbers}, spreadsheetId, pasteRange, {valueInputOption: "RAW"}); // Modified
  }
}

Note :

• If the error related to API occurs, please confirm whether Sheets API is enabled at Advanced Google Services and API console, again.

Reference :

• Method: spreadsheets.values.update

For your 2nd question

If you want to convert the gridrange to a1notation, you can see a sample at the document and this gist.

If I misunderstand your question, I'm sorry.