Lettura e scrittura dei valori delle celle

I fogli di lavoro possono avere più fogli e ogni foglio contiene un numero qualsiasi di righe o colonne. Una cella è una posizione all'intersezione di una determinata riga e colonna e potrebbe contenere un valore dei dati. L'API Fogli Google fornisce la risorsa spreadsheets.values per consentire la lettura e la scrittura di valori.

In questa pagina vengono descritte le nozioni di base sull'utilizzo della risorsa spreadsheets.values. Se devi inserire righe o aggiornare la formattazione e altre proprietà di un foglio, devi utilizzare il metodo spreadsheets.batchUpdate descritto in Aggiornare i fogli di lavoro.

Metodi

La risorsa spreadsheets.values fornisce i seguenti metodi per leggere e scrivere valori, ciascuno per un'attività specifica:

Accesso all'intervallo	Lettura	Scrittura
Intervallo singolo	spreadsheets.values.get	spreadsheets.values.update
Intervalli multipli	spreadsheets.values.batchGet	spreadsheets.values.batchUpdate
Aggiunta		spreadsheets.values.append

In generale, è una buona idea combinare più letture o aggiornamenti con i metodi batchGet e batchUpdate (rispettivamente), poiché ciò migliora l'efficienza.

Puoi trovare esempi di ognuno di questi metodi nelle pagine di esempio Lettura di base e Scrittura di base. Per visualizzare tutti gli esempi, consulta la pagina di riepilogo degli esempi.

Letto

Per leggere i valori dei dati da un foglio, sono necessari l'ID del foglio di lavoro e la notazione A1 per l'intervallo. Se specifichi l'intervallo senza ID foglio (A1:B2), la richiesta viene eseguita sul primo foglio del foglio di lavoro. Per ulteriori informazioni sugli ID fogli di lavoro e sulla notazione A1, consulta la Panoramica dell'API Fogli Google.

Diversi parametri di query facoltativi controllano il formato dell'output:

Parametro formato	Valore predefinito
majorDimension	RIGHE
valueRenderOption	FORMATTED_VALUE
dateTimeRenderOption	SERIAL_NUMBER

Tieni presente che devi usare dateTimeRenderOption solo se valueRenderOption non è FORMATTED_VALUE.

Non esiste un limite esplicito alla quantità di dati restituiti. Gli errori non restituiscono dati. Le righe e le colonne finali vuote vengono omesse.

Di seguito sono descritti i metodi singolo e batch get. Per esempi di operazioni di lettura di base, consulta Lettura di base.

Leggi un singolo intervallo

Per leggere un singolo intervallo di valori da un foglio di lavoro, utilizza una richiesta spreadsheets.values.get:

Apps Script

sheets/api/sheet_snippets.gs

Visualizza su GitHub

/**
 * Gets the values of the cells in the specified range
 * @param {string} spreadsheetId id of the spreadsheet
 * @param {string} range specifying the start and end cells of the range
 * @returns {*} Values in the range
 */
Snippets.prototype.getValues = function(spreadsheetId, range) {
  // This code uses the Sheets Advanced Service, but for most use cases
  // the built-in method SpreadsheetApp.getActiveSpreadsheet()
  //     .getRange(range).getValues(values) is more appropriate.
  try {
    const result = Sheets.Spreadsheets.Values.get(spreadsheetId, range);
    const numRows = result.values ? result.values.length : 0;
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/GetValues.java

Visualizza su GitHub

import com.google.api.client.googleapis.json.GoogleJsonError;
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
import com.google.api.client.http.HttpRequestInitializer;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.ValueRange;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import java.io.IOException;
import java.util.Collections;

/* Class to demonstrate the use of Spreadsheet Get Values API */
public class GetValues {
  /**
   * Returns a range of values from a spreadsheet.
   *
   * @param spreadsheetId - Id of the spreadsheet.
   * @param range         - Range of cells of the spreadsheet.
   * @return Values in the range
   * @throws IOException - if credentials file not found.
   */
  public static ValueRange getValues(String spreadsheetId, String range) throws IOException {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
    GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
        .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
    HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
        credentials);

    // Create the sheets API client
    Sheets service = new Sheets.Builder(new NetHttpTransport(),
        GsonFactory.getDefaultInstance(),
        requestInitializer)
        .setApplicationName("Sheets samples")
        .build();

    ValueRange result = null;
    try {
      // Gets the values of the cells in the specified range.
      result = service.spreadsheets().values().get(spreadsheetId, range).execute();
      int numRows = result.getValues() != null ? result.getValues().size() : 0;
      System.out.printf("%d rows retrieved.", numRows);
    } catch (GoogleJsonResponseException e) {
      // TODO(developer) - handle error appropriately
      GoogleJsonError error = e.getDetails();
      if (error.getCode() == 404) {
        System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId);
      } else {
        throw e;
      }
    }
    return result;
  }
}

JavaScript

sheets/snippets/sheets_get_values.js

Visualizza su GitHub

function getValues(spreadsheetId, range, callback) {
  try {
    gapi.client.sheets.spreadsheets.values.get({
      spreadsheetId: spreadsheetId,
      range: range,
    }).then((response) => {
      const result = response.result;
      const numRows = result.values ? result.values.length : 0;
      console.log(`${numRows} rows retrieved.`);
      if (callback) callback(response);
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_get_values.js

Visualizza su GitHub

/**
 * Gets cell values from a Spreadsheet.
 * @param {string} spreadsheetId The spreadsheet ID.
 * @param {string} range The sheet range.
 * @return {obj} spreadsheet information
 */
async function getValues(spreadsheetId, range) {
  const {GoogleAuth} = require('google-auth-library');
  const {google} = require('googleapis');

  const auth = new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  });

  const service = google.sheets({version: 'v4', auth});
  try {
    const result = await service.spreadsheets.values.get({
      spreadsheetId,
      range,
    });
    const numRows = result.data.values ? result.data.values.length : 0;
    console.log(`${numRows} rows retrieved.`);
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetGetValues.php

Visualizza su GitHub

use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;
/**
 * get values of a particular spreadsheet(by Id and range).
 */
function getValues($spreadsheetId, $range)
    {   
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
        $client = new Google\Client();
        $client->useApplicationDefaultCredentials();
        $client->addScope(Google\Service\Drive::DRIVE);
        $service = new Google_Service_Sheets($client);
        $result = $service->spreadsheets_values->get($spreadsheetId, $range);
        try{
        $numRows = $result->getValues() != null ? count($result->getValues()) : 0;
        printf("%d rows retrieved.", $numRows);
        return $result;
    }
        catch(Exception $e) {
            // TODO(developer) - handle error appropriately
            echo 'Message: ' .$e->getMessage();
        }
    }

Python

sheets/snippets/sheets_get_values.py

Visualizza su GitHub

import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def get_values(spreadsheet_id, range_name):
  """
  Creates the batch_update the user has access to.
  Load pre-authorized user credentials from the environment.
  TODO(developer) - See https://developers.google.com/identity
  for guides on implementing OAuth2 for the application.
  """
  creds, _ = google.auth.default()
  # pylint: disable=maybe-no-member
  try:
    service = build("sheets", "v4", credentials=creds)

    result = (
        service.spreadsheets()
        .values()
        .get(spreadsheetId=spreadsheet_id, range=range_name)
        .execute()
    )
    rows = result.get("values", [])
    print(f"{len(rows)} rows retrieved")
    return result
  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id, and range_name
  get_values("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k", "A1:C2")

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb

Visualizza su GitHub

result = service.get_spreadsheet_values(spreadsheet_id, range_name)
num_rows = result.values ? result.values.length : 0
puts "#{num_rows} rows received."

La risposta a questa richiesta viene restituita come oggetto ValueRange.

Lettura di più intervalli

Per leggere più intervalli di valori discontinui da un foglio di lavoro, utilizza una richiesta spreadsheets.values.batchGet che ti consenta di specificare diversi intervalli da recuperare:

Apps Script

sheets/api/sheet_snippets.gs

Visualizza su GitHub

/**
 * Get the values in the specified ranges
 * @param {string} spreadsheetId spreadsheet's ID
 * @param {list<string>} _ranges The span of ranges
 * @returns {*} spreadsheet information and values
 */
Snippets.prototype.batchGetValues = (spreadsheetId,
  _ranges) => {
  // This code uses the Sheets Advanced Service, but for most use cases
  // the built-in method SpreadsheetApp.getActiveSpreadsheet()
  //     .getRange(range).getValues(values) is more appropriate.
  let ranges = [
    //Range names ...
  ];
  try {
    const result =
      Sheets.Spreadsheets.Values.batchGet(spreadsheetId, {ranges: ranges});
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/BatchGetValues.java

Visualizza su GitHub

import com.google.api.client.googleapis.json.GoogleJsonError;
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
import com.google.api.client.http.HttpRequestInitializer;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.BatchGetValuesResponse;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import java.io.IOException;
import java.util.Collections;
import java.util.List;

/* Class to demonstrate the use of Spreadsheet Batch Get Values API */
public class BatchGetValues {
  /**
   * Returns one or more ranges of values from a spreadsheet.
   *
   * @param spreadsheetId - Id of the spreadsheet.
   * @param ranges        - Range of cells of the spreadsheet.
   * @return Values in the range
   * @throws IOException - if credentials file not found.
   */
  public static BatchGetValuesResponse batchGetValues(String spreadsheetId,
                                                      List<String> ranges)
      throws IOException {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
    GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
        .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
    HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
        credentials);

    // Create the sheets API client
    Sheets service = new Sheets.Builder(new NetHttpTransport(),
        GsonFactory.getDefaultInstance(),
        requestInitializer)
        .setApplicationName("Sheets samples")
        .build();

    BatchGetValuesResponse result = null;
    try {
      // Gets the values of the cells in the specified range.
      result = service.spreadsheets().values().batchGet(spreadsheetId)
          .setRanges(ranges).execute();
      System.out.printf("%d ranges retrieved.", result.getValueRanges().size());
    } catch (GoogleJsonResponseException e) {
      // TODO(developer) - handle error appropriately
      GoogleJsonError error = e.getDetails();
      if (error.getCode() == 404) {
        System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId);
      } else {
        throw e;
      }
    }
    return result;
  }
}

JavaScript

sheets/snippets/sheets_batch_get_values.js

Visualizza su GitHub

function batchGetValues(spreadsheetId, _ranges, callback) {
  let ranges = [
    // Range names ...
  ];
  ranges = _ranges;
  try {
    gapi.client.sheets.spreadsheets.values.batchGet({
      spreadsheetId: spreadsheetId,
      ranges: ranges,
    }).then((response) => {
      const result = response.result;
      console.log(`${result.valueRanges.length} ranges retrieved.`);
      if (callback) callback(response);
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_batch_get_values.js

Visualizza su GitHub

/**
 * Batch gets cell values from a Spreadsheet.
 * @param {string} spreadsheetId The spreadsheet ID.
 * @param {string} _ranges The mock sheet range.
 * @return {obj} spreadsheet information
 */
async function batchGetValues(spreadsheetId, _ranges) {
  const {GoogleAuth} = require('google-auth-library');
  const {google} = require('googleapis');

  const auth = new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  });

  const service = google.sheets({version: 'v4', auth});
  let ranges = [
    // Range names ...
  ];
  try {
    const result = await service.spreadsheets.values.batchGet({
      spreadsheetId,
      ranges,
    });
    console.log(`${result.data.valueRanges.length} ranges retrieved.`);
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetBatchGetValues.php

Visualizza su GitHub

use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets;
/**
 * method to get a spreadsheet values in batch
 */

function batchGetValues($spreadsheetId)
    {
        /* Load pre-authorized user credentials from the environment.
        TODO(developer) - See https://developers.google.com/identity for
        guides on implementing OAuth2 for your application. */       
        $client = new Google\Client();
        $client->useApplicationDefaultCredentials();
        $client->addScope(Google\Service\Drive::DRIVE);
        $service = new Google_Service_Sheets($client);
        try{
            $ranges = 'Sheet1!A1:B2';
            $params = array(
                'ranges' => $ranges
            );
            //execute the request
            $result = $service->spreadsheets_values->batchGet($spreadsheetId, $params);
            printf("%d ranges retrieved.", count($result->getValueRanges()));
            return $result;
        }
        catch(Exception $e) {
            // TODO(developer) - handle error appropriately
            echo 'Message: ' .$e->getMessage();
          }
        }

Python

sheets/snippets/sheets_batch_get_values.py

Visualizza su GitHub

import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def batch_get_values(spreadsheet_id, _range_names):
  """
  Creates the batch_update the user has access to.
  Load pre-authorized user credentials from the environment.
  TODO(developer) - See https://developers.google.com/identity
  for guides on implementing OAuth2 for the application.
  """
  creds, _ = google.auth.default()
  # pylint: disable=maybe-no-member
  try:
    service = build("sheets", "v4", credentials=creds)
    range_names = [
        # Range names ...
    ]
    result = (
        service.spreadsheets()
        .values()
        .batchGet(spreadsheetId=spreadsheet_id, ranges=range_names)
        .execute()
    )
    ranges = result.get("valueRanges", [])
    print(f"{len(ranges)} ranges retrieved")
    return result
  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id, and range_name

  batch_get_values("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k", "A1:C2")

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb

Visualizza su GitHub

range_names = [
  # Range names ...
]
result = service.batch_get_spreadsheet_values(spreadsheet_id,
                                              ranges: range_names)
puts "#{result.value_ranges.length} ranges retrieved."

La risposta a questa richiesta viene restituita come oggetto BatchGetValuesResponse contenente spreadsheetId e un elenco di oggetti ValueRange.

Scrittura

Per scrivere in un foglio sono necessari l'ID del foglio di lavoro, l'intervallo di celle nella notazione A1 e i dati che vuoi scrivere all'interno di un oggetto corpo della richiesta appropriato. Per ulteriori informazioni sugli ID fogli di lavoro e sulla notazione A1, consulta la panoramica dell'API Fogli Google.

Gli aggiornamenti richiedono un parametro ValueInputOption valido. Per gli aggiornamenti singolari, questo è un parametro di query obbligatorio. Per gli aggiornamenti batch, questo parametro è obbligatorio nel corpo della richiesta. L'elemento ValueInputOption controlla il modo in cui i dati di input devono essere interpretati e se le stringhe di input vengono analizzate o meno, come descritto nella seguente tabella:

ValueInputOption	Descrizione
RAW	L'input non viene analizzato e viene inserito come stringa. Ad esempio, l'input "=1+2" inserisce la stringa, non la formula, "=1+2" nella cella. I valori non stringa, come booleani o numeri, vengono sempre gestiti come RAW.
USER_ENTERED	L'input viene analizzato esattamente come se fosse stato inserito nell'interfaccia utente di Fogli. Ad esempio, "1 marzo 2016" diventa una data e "=1+2" diventa una formula. È anche possibile dedurre i formati, quindi "100,15 $" diventa un numero con formattazione della valuta.

Di seguito sono descritti i metodi di aggiornamento singolare e batch. Per esempi di operazioni di scrittura di base, consulta Scrittura di base.

Scrivere in un singolo intervallo

Per scrivere dati in un singolo intervallo, utilizza una richiesta spreadsheets.values.update:

Apps Script

sheets/api/sheet_snippets.gs

Visualizza su GitHub

/**
 * Updates the values in the specified range
 * @param {string} spreadsheetId spreadsheet's ID
 * @param {string} range the range of cells in spreadsheet
 * @param {string} valueInputOption determines how the input should be interpreted
 * @see
 * https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
 * @param {list<list<string>>} _values list of string lists to input
 * @returns {*} spreadsheet with updated values
 */
Snippets.prototype.updateValues = (spreadsheetId, range,
  valueInputOption, _values) => {
  // This code uses the Sheets Advanced Service, but for most use cases
  // the built-in method SpreadsheetApp.getActiveSpreadsheet()
  //     .getRange(range).setValues(values) is more appropriate.
  let values = [
    [
      // Cell values ...
    ]
    // Additional rows ...
  ];

  try {
    let valueRange = Sheets.newValueRange();
    valueRange.values = values;
    const result = Sheets.Spreadsheets.Values.update(valueRange,
      spreadsheetId, range, {valueInputOption: valueInputOption});
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/UpdateValues.java

Visualizza su GitHub

import com.google.api.client.googleapis.json.GoogleJsonError;
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
import com.google.api.client.http.HttpRequestInitializer;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.UpdateValuesResponse;
import com.google.api.services.sheets.v4.model.ValueRange;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import java.io.IOException;
import java.util.Collections;
import java.util.List;

/* Class to demonstrate the use of Spreadsheet Update Values API */
public class UpdateValues {
  /**
   * Sets values in a range of a spreadsheet.
   *
   * @param spreadsheetId    - Id of the spreadsheet.
   * @param range            - Range of cells of the spreadsheet.
   * @param valueInputOption - Determines how input data should be interpreted.
   * @param values           - List of rows of values to input.
   * @return spreadsheet with updated values
   * @throws IOException - if credentials file not found.
   */
  public static UpdateValuesResponse updateValues(String spreadsheetId,
                                                  String range,
                                                  String valueInputOption,
                                                  List<List<Object>> values)
      throws IOException {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
    GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
        .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
    HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
        credentials);

    // Create the sheets API client
    Sheets service = new Sheets.Builder(new NetHttpTransport(),
        GsonFactory.getDefaultInstance(),
        requestInitializer)
        .setApplicationName("Sheets samples")
        .build();

    UpdateValuesResponse result = null;
    try {
      // Updates the values in the specified range.
      ValueRange body = new ValueRange()
          .setValues(values);
      result = service.spreadsheets().values().update(spreadsheetId, range, body)
          .setValueInputOption(valueInputOption)
          .execute();
      System.out.printf("%d cells updated.", result.getUpdatedCells());
    } catch (GoogleJsonResponseException e) {
      // TODO(developer) - handle error appropriately
      GoogleJsonError error = e.getDetails();
      if (error.getCode() == 404) {
        System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId);
      } else {
        throw e;
      }
    }
    return result;
  }
}

JavaScript

sheets/snippets/sheets_update_values.js

Visualizza su GitHub

function updateValues(spreadsheetId, range, valueInputOption, _values, callback) {
  let values = [
    [
      // Cell values ...
    ],
    // Additional rows ...
  ];
  values = _values;
  const body = {
    values: values,
  };
  try {
    gapi.client.sheets.spreadsheets.values.update({
      spreadsheetId: spreadsheetId,
      range: range,
      valueInputOption: valueInputOption,
      resource: body,
    }).then((response) => {
      const result = response.result;
      console.log(`${result.updatedCells} cells updated.`);
      if (callback) callback(response);
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_update_values.js

Visualizza su GitHub

/**
 * Updates values in a Spreadsheet.
 * @param {string} spreadsheetId The spreadsheet ID.
 * @param {string} range The range of values to update.
 * @param {object} valueInputOption Value update options.
 * @param {(string[])[]} _values A 2d array of values to update.
 * @return {obj} spreadsheet information
 */
async function updateValues(spreadsheetId, range, valueInputOption, _values) {
  const {GoogleAuth} = require('google-auth-library');
  const {google} = require('googleapis');

  const auth = new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  });

  const service = google.sheets({version: 'v4', auth});
  let values = [
    [
      // Cell values ...
    ],
    // Additional rows ...
  ];
  const resource = {
    values,
  };
  try {
    const result = await service.spreadsheets.values.update({
      spreadsheetId,
      range,
      valueInputOption,
      resource,
    });
    console.log('%d cells updated.', result.data.updatedCells);
    return result;
  } catch (err) {
    // TODO (Developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetUpdateValues.php

Visualizza su GitHub

use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets\ValueRange;


function updateValues($spreadsheetId, $range, $valueInputOption)
    {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
        $client = new Google\Client();
        $client->useApplicationDefaultCredentials();
        $client->addScope(Google\Service\Drive::DRIVE);
        $service = new Google_Service_Sheets($client);
        try{
        $values = [["sample", 'values']];

        $body = new Google_Service_Sheets_ValueRange([
            'values' => $values
        ]);
        $params = [
            'valueInputOption' => $valueInputOption
        ];
        //executing the request
        $result = $service->spreadsheets_values->update($spreadsheetId, $range,
        $body, $params);
        printf("%d cells updated.", $result->getUpdatedCells());
        return $result;
    }
    catch(Exception $e) {
            // TODO(developer) - handle error appropriately
            echo 'Message: ' .$e->getMessage();
          }
    }

Python

sheets/snippets/sheets_update_values.py

Visualizza su GitHub

import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def update_values(spreadsheet_id, range_name, value_input_option, _values):
  """
  Creates the batch_update the user has access to.
  Load pre-authorized user credentials from the environment.
  TODO(developer) - See https://developers.google.com/identity
  for guides on implementing OAuth2 for the application.
  """
  creds, _ = google.auth.default()
  # pylint: disable=maybe-no-member
  try:
    service = build("sheets", "v4", credentials=creds)
    values = [
        [
            # Cell values ...
        ],
        # Additional rows ...
    ]
    body = {"values": values}
    result = (
        service.spreadsheets()
        .values()
        .update(
            spreadsheetId=spreadsheet_id,
            range=range_name,
            valueInputOption=value_input_option,
            body=body,
        )
        .execute()
    )
    print(f"{result.get('updatedCells')} cells updated.")
    return result
  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id,  range_name, value_input_option and  _values
  update_values(
      "1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k",
      "A1:C2",
      "USER_ENTERED",
      [["A", "B"], ["C", "D"]],
  )

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb

Visualizza su GitHub

values = [
  [
    # Cell values ...
  ]
  # Additional rows ...
]
data = [
  {
    range:  range_name,
    values: values
  },
  # Additional ranges to update ...
]
value_range_object = Google::Apis::SheetsV4::ValueRange.new(range:  range_name,
                                                            values: values)
result = service.update_spreadsheet_value(spreadsheet_id,
                                          range_name,
                                          value_range_object,
                                          value_input_option: value_input_option)
puts "#{result.updated_cells} cells updated."

Il corpo della richiesta di aggiornamento deve essere un oggetto ValueRange, anche se l'unico campo obbligatorio è values. Se range è specificato, deve corrispondere all'intervallo nell'URL. In ValueRange, puoi specificare facoltativamente majorDimension. Per impostazione predefinita, viene utilizzato il criterio ROWS. Se COLUMNS è specificato, ogni array interno viene scritto in una colonna anziché in una riga.

Durante l'aggiornamento, i valori senza dati vengono ignorati. Per cancellare i dati, utilizza una stringa vuota ("").

Un parametro range può specificare più celle (ad esempio A1:D5) o una singola cella (ad esempio A1). Se specifica più celle, i dati di input devono essere compresi in quell'intervallo. Se specifica una singola cella, i dati di input iniziano in questa coordinata e possono estendere un numero qualsiasi di righe o colonne.

Scrivere più intervalli

Se vuoi scrivere più intervalli discontinui, puoi utilizzare una richiesta spreadsheets.values.batchUpdate:

Apps Script

sheets/api/sheet_snippets.gs

Visualizza su GitHub

/**
 * Updates the values in the specified range
 * @param {string} spreadsheetId spreadsheet's ID
 * @param {string} range range of cells of the spreadsheet
 * @param {string} valueInputOption determines how the input should be interpreted
 * @see
 * https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
 * @param {list<list<string>>} _values list of string values to input
 * @returns {*} spreadsheet with updated values
 */
Snippets.prototype.batchUpdateValues =
  (spreadsheetId, range, valueInputOption,
    _values) => {
    // This code uses the Sheets Advanced Service, but for most use cases
    // the built-in method SpreadsheetApp.getActiveSpreadsheet()
    //     .getRange(range).setValues(values) is more appropriate.
    let values = [
      [
        // Cell values ...
      ]
      // Additional rows ...
    ];

    try {
      let valueRange = Sheets.newValueRange();
      valueRange.range = range;
      valueRange.values = values;

      let batchUpdateRequest = Sheets.newBatchUpdateValuesRequest();
      batchUpdateRequest.data = valueRange;
      batchUpdateRequest.valueInputOption = valueInputOption;

      const result = Sheets.Spreadsheets.Values.batchUpdate(batchUpdateRequest,
        spreadsheetId);
      return result;
    } catch (err) {
      // TODO (developer) - Handle exception
      console.log('Failed with error %s', err.message);
    }
  };

Java

sheets/snippets/src/main/java/BatchUpdateValues.java

Visualizza su GitHub

import com.google.api.client.googleapis.json.GoogleJsonError;
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
import com.google.api.client.http.HttpRequestInitializer;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.BatchUpdateValuesRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateValuesResponse;
import com.google.api.services.sheets.v4.model.ValueRange;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/* Class to demonstrate the use of Spreadsheet Batch Update Values API */
public class BatchUpdateValues {
  /**
   * Set values in one or more ranges of spreadsheet.
   *
   * @param spreadsheetId    - Id of the spreadsheet.
   * @param range            - Range of cells of the spreadsheet.
   * @param valueInputOption - Determines how input data should be interpreted.
   * @param values           - list of rows of values to input.
   * @return spreadsheet with updated values
   * @throws IOException - if credentials file not found.
   */
  public static BatchUpdateValuesResponse batchUpdateValues(String spreadsheetId,
                                                            String range,
                                                            String valueInputOption,
                                                            List<List<Object>> values)
      throws IOException {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
    GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
        .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
    HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
        credentials);

    // Create the sheets API client
    Sheets service = new Sheets.Builder(new NetHttpTransport(),
        GsonFactory.getDefaultInstance(),
        requestInitializer)
        .setApplicationName("Sheets samples")
        .build();

    List<ValueRange> data = new ArrayList<>();
    data.add(new ValueRange()
        .setRange(range)
        .setValues(values));

    BatchUpdateValuesResponse result = null;
    try {
      // Updates the values in the specified range.
      BatchUpdateValuesRequest body = new BatchUpdateValuesRequest()
          .setValueInputOption(valueInputOption)
          .setData(data);
      result = service.spreadsheets().values().batchUpdate(spreadsheetId, body).execute();
      System.out.printf("%d cells updated.", result.getTotalUpdatedCells());
    } catch (GoogleJsonResponseException e) {
      // TODO(developer) - handle error appropriately
      GoogleJsonError error = e.getDetails();
      if (error.getCode() == 404) {
        System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId);
      } else {
        throw e;
      }
    }
    return result;
  }
}

JavaScript

sheets/snippets/sheets_batch_update_values.js

Visualizza su GitHub

function batchUpdateValues(spreadsheetId, range, valueInputOption, _values, callback) {
  let values = [
    [
      // Cell values ...
    ],
    // Additional rows ...
  ];
  values = _values;
  const data = [];
  data.push({
    range: range,
    values: values,
  });
  // Additional ranges to update.

  const body = {
    data: data,
    valueInputOption: valueInputOption,
  };
  try {
    gapi.client.sheets.spreadsheets.values.batchUpdate({
      spreadsheetId: spreadsheetId,
      resource: body,
    }).then((response) => {
      const result = response.result;
      console.log(`${result.totalUpdatedCells} cells updated.`);
      if (callback) callback(response);
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_batch_update_values.js

Visualizza su GitHub

/**
 * Batch Updates values in a Spreadsheet.
 * @param {string} spreadsheetId The spreadsheet ID.
 * @param {string} range The range of values to update.
 * @param {object} valueInputOption Value update options.
 * @param {(string[])[]} _values A 2d array of values to update.
 * @return {obj} spreadsheet information
 */
async function batchUpdateValues(
    spreadsheetId,
    range,
    valueInputOption,
    _values,
) {
  const {GoogleAuth} = require('google-auth-library');
  const {google} = require('googleapis');

  const auth = new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  });

  const service = google.sheets({version: 'v4', auth});
  let values = [
    [
      // Cell values ...
    ],
    // Additional rows ...
  ];
  const data = [
    {
      range,
      values,
    },
  ];
  // Additional ranges to update ...
  const resource = {
    data,
    valueInputOption,
  };
  try {
    const result = await service.spreadsheets.values.batchUpdate({
      spreadsheetId,
      resource,
    });
    console.log('%d cells updated.', result.data.totalUpdatedCells);
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetBatchUpdateValues.php

Visualizza su GitHub

/**
 * to update values in batch for a particular spreadsheet
 */
use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets;

function batchUpdateValues($spreadsheetId, $range, $valueInputOption)
    {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
        $client = new Google\Client();
        $client->useApplicationDefaultCredentials();
        $client->addScope(Google\Service\Drive::DRIVE);
        $service = new Google_Service_Sheets($client);
        $values = [];
        try{

            $data[] = new Google_Service_Sheets_ValueRange([
                'range' => $range,
            'values' => $values
        ]);
        $body = new Google_Service_Sheets_BatchUpdateValuesRequest([
            'valueInputOption' => $valueInputOption,
            'data' => $data
        ]);
        $result = $service->spreadsheets_values->batchUpdate($spreadsheetId, $body);
        printf("%d cells updated.", $result->getTotalUpdatedCells());
        return $result;
    }

        catch(Exception $e) {
            // TODO(developer) - handle error appropriately
            echo 'Message: ' .$e->getMessage();
          }
    }

Python

sheets/snippets/sheets_batch_update_values.py

Visualizza su GitHub

import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def batch_update_values(
    spreadsheet_id, range_name, value_input_option, _values
):
  """
  Creates the batch_update the user has access to.
  Load pre-authorized user credentials from the environment.
  TODO(developer) - See https://developers.google.com/identity
  for guides on implementing OAuth2 for the application.
  """
  creds, _ = google.auth.default()
  # pylint: disable=maybe-no-member
  try:
    service = build("sheets", "v4", credentials=creds)

    values = [
        [
            # Cell values ...
        ],
        # Additional rows
    ]
    data = [
        {"range": range_name, "values": values},
        # Additional ranges to update ...
    ]
    body = {"valueInputOption": value_input_option, "data": data}
    result = (
        service.spreadsheets()
        .values()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )
    print(f"{(result.get('totalUpdatedCells'))} cells updated.")
    return result
  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id, range_name value_input_option and _values)
  batch_update_values(
      "1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k",
      "A1:C2",
      "USER_ENTERED",
      [["F", "B"], ["C", "D"]],
  )

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb

Visualizza su GitHub

values = [
  [
    # Cell values ...
  ]
  # Additional rows ...
]
data = [
  {
    range:  range_name,
    values: values
  },
  # Additional ranges to update ...
]
batch_update_values = Google::Apis::SheetsV4::BatchUpdateValuesRequest.new(
  data:               data,
  value_input_option: value_input_option
)
result = service.batch_update_values(spreadsheet_id, batch_update_values)
puts "#{result.total_updated_cells} cells updated."

Il corpo della richiesta di aggiornamento batch deve essere un oggetto BatchUpdateValuesRequest, che contiene un ValueInputOption e un elenco di oggetti ValueRange (uno per ogni intervallo scritto). Ogni oggetto ValueRange specifica i propri range, majorDimension e i propri dati di input.

Aggiungi valori

Per aggiungere dati dopo una tabella di dati in un foglio, utilizza una richiesta spreadsheets.values.append:

Apps Script

sheets/api/sheet_snippets.gs

Visualizza su GitHub

/**
 * Appends values to the specified range
 * @param {string} spreadsheetId spreadsheet's ID
 * @param {string} range range of cells in the spreadsheet
 * @param valueInputOption determines how the input should be interpreted
 * @see
 * https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
 * @param {list<string>} _values list of rows of values to input
 * @returns {*} spreadsheet with appended values
 */
Snippets.prototype.appendValues = (spreadsheetId, range,
  valueInputOption, _values) => {
  let values = [
    [
      // Cell values ...
    ]
    // Additional rows ...
  ];
  try {
    let valueRange = Sheets.newRowData();
    valueRange.values = values;

    let appendRequest = Sheets.newAppendCellsRequest();
    appendRequest.sheetId = spreadsheetId;
    appendRequest.rows = [valueRange];

    const result = Sheets.Spreadsheets.Values.append(valueRange, spreadsheetId,
      range, {valueInputOption: valueInputOption});
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/AppendValues.java

Visualizza su GitHub

import com.google.api.client.googleapis.json.GoogleJsonError;
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
import com.google.api.client.http.HttpRequestInitializer;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.AppendValuesResponse;
import com.google.api.services.sheets.v4.model.ValueRange;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import java.io.IOException;
import java.util.Collections;
import java.util.List;

/* Class to demonstrate the use of Spreadsheet Append Values API */
public class AppendValues {
  /**
   * Appends values to a spreadsheet.
   *
   * @param spreadsheetId    - Id of the spreadsheet.
   * @param range            - Range of cells of the spreadsheet.
   * @param valueInputOption - Determines how input data should be interpreted.
   * @param values           - list of rows of values to input.
   * @return spreadsheet with appended values
   * @throws IOException - if credentials file not found.
   */
  public static AppendValuesResponse appendValues(String spreadsheetId,
                                                  String range,
                                                  String valueInputOption,
                                                  List<List<Object>> values)
      throws IOException {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
    GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
        .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
    HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
        credentials);

    // Create the sheets API client
    Sheets service = new Sheets.Builder(new NetHttpTransport(),
        GsonFactory.getDefaultInstance(),
        requestInitializer)
        .setApplicationName("Sheets samples")
        .build();

    AppendValuesResponse result = null;
    try {
      // Append values to the specified range.
      ValueRange body = new ValueRange()
          .setValues(values);
      result = service.spreadsheets().values().append(spreadsheetId, range, body)
          .setValueInputOption(valueInputOption)
          .execute();
      // Prints the spreadsheet with appended values.
      System.out.printf("%d cells appended.", result.getUpdates().getUpdatedCells());
    } catch (GoogleJsonResponseException e) {
      // TODO(developer) - handle error appropriately
      GoogleJsonError error = e.getDetails();
      if (error.getCode() == 404) {
        System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId);
      } else {
        throw e;
      }
    }
    return result;
  }
}

JavaScript

sheets/snippets/sheets_append_values.js

Visualizza su GitHub

function appendValues(spreadsheetId, range, valueInputOption, _values, callback) {
  let values = [
    [
      // Cell values ...
    ],
    // Additional rows ...
  ];
  values = _values;
  const body = {
    values: values,
  };
  try {
    gapi.client.sheets.spreadsheets.values.append({
      spreadsheetId: spreadsheetId,
      range: range,
      valueInputOption: valueInputOption,
      resource: body,
    }).then((response) => {
      const result = response.result;
      console.log(`${result.updates.updatedCells} cells appended.`);
      if (callback) callback(response);
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_append_values.js

Visualizza su GitHub

/**
 * Appends values in a Spreadsheet.
 * @param {string} spreadsheetId The spreadsheet ID.
 * @param {string} range The range of values to append.
 * @param {object} valueInputOption Value input options.
 * @param {(string[])[]} _values A 2d array of values to append.
 * @return {obj} spreadsheet information
 */
async function appendValues(spreadsheetId, range, valueInputOption, _values) {
  const {GoogleAuth} = require('google-auth-library');
  const {google} = require('googleapis');

  const auth = new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  });

  const service = google.sheets({version: 'v4', auth});
  let values = [
    [
      // Cell values ...
    ],
    // Additional rows ...
  ];
  const resource = {
    values,
  };
  try {
    const result = await service.spreadsheets.values.append({
      spreadsheetId,
      range,
      valueInputOption,
      resource,
    });
    console.log(`${result.data.updates.updatedCells} cells appended.`);
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetAppendValues.php

Visualizza su GitHub

use Google\Client;
use Google\Service\Sheets;


function appendValues($spreadsheetId, $range, $valueInputOption)
{
    /* Load pre-authorized user credentials from the environment.
       TODO(developer) - See https://developers.google.com/identity for
        guides on implementing OAuth2 for your application. */
    $client = new Google\Client();
    $client->useApplicationDefaultCredentials();
    $client->addScope('https://www.googleapis.com/auth/spreadsheets');
    $service = new Google\Service\Sheets($client);
    try {
        $values = []; //add the values to be appended
        //execute the request
        $body = new Google_Service_Sheets_ValueRange([
            'values' => $values
        ]);
        $params = [
            'valueInputOption' => $valueInputOption
        ];
        $result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params);
        printf("%d cells appended.", $result->getUpdates()->getUpdatedCells());
        return $result;
    } catch (Exception $e) {
        // TODO(developer) - handle error appropriately
        echo 'Message: ' . $e->getMessage();
    }

Python

sheets/snippets/sheets_append_values.py

Visualizza su GitHub

import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def append_values(spreadsheet_id, range_name, value_input_option, _values):
  """
  Creates the batch_update the user has access to.
  Load pre-authorized user credentials from the environment.
  TODO(developer) - See https://developers.google.com/identity
  for guides on implementing OAuth2 for the application.
  """
  creds, _ = google.auth.default()
  # pylint: disable=maybe-no-member
  try:
    service = build("sheets", "v4", credentials=creds)

    values = [
        [
            # Cell values ...
        ],
        # Additional rows ...
    ]
    body = {"values": values}
    result = (
        service.spreadsheets()
        .values()
        .append(
            spreadsheetId=spreadsheet_id,
            range=range_name,
            valueInputOption=value_input_option,
            body=body,
        )
        .execute()
    )
    print(f"{(result.get('updates').get('updatedCells'))} cells appended.")
    return result

  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id, range_name value_input_option and _values)
  append_values(
      "1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k",
      "A1:C2",
      "USER_ENTERED",
      [["F", "B"], ["C", "D"]],
  )

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb

Visualizza su GitHub

values = [
  [
    # Cell values ...
  ],
  # Additional rows ...
]
value_range = Google::Apis::SheetsV4::ValueRange.new(values: values)
result = service.append_spreadsheet_value(spreadsheet_id,
                                          range_name,
                                          value_range,
                                          value_input_option: value_input_option)
puts "#{result.updates.updated_cells} cells appended."

Il corpo della richiesta di aggiornamento deve essere un oggetto ValueRange, anche se l'unico campo obbligatorio è values. Se range è specificato, deve corrispondere all'intervallo nell'URL. In ValueRange, puoi specificare facoltativamente majorDimension. Per impostazione predefinita, viene utilizzato il criterio ROWS. Se COLUMNS è specificato, ogni array interno viene scritto in una colonna anziché in una riga.

Il parametro majorDimension non controlla se i dati vengono aggiunti alla tabella sotto forma di righe o colonne. I dati vengono sempre aggiunti alle righe successive. Il parametro controlla solo il modo in cui vengono letti i dati di input.

L'intervallo di input viene utilizzato per cercare dati esistenti e trovare una "tabella" all'interno dell'intervallo. I valori vengono aggiunti alla riga successiva della tabella, a partire dalla prima colonna della tabella. Ad esempio, considera Sheet1 che ha il seguente aspetto:

	A	B	C	D	E
1	x	y	z
2	x	y	z
3
4		x	y
5			y	z
6		x	y	z
7

Nel foglio sono presenti due tabelle: A1:C2 e B4:D6. I valori aggiunti iniziano da B7 per tutti i seguenti input range:

• Sheet1, perché esaminerà tutti i dati del foglio e determinerà che la tabella in B4:D6 è l'ultima.
• B4 o C5:D5, perché entrambi nella tabella B4:D6.
• B2:D4, perché l'ultima tabella nell'intervallo è la tabella B4:D6 (nonostante contenga anche la tabella A1:C2).
• A3:G10, perché l'ultima tabella dell'intervallo è la tabella B4:D6 (nonostante inizi prima e finisca dopo).

I seguenti input di range non inizieranno a scrivere in B7:

• A1 inizierà a scrivere alle ore A3, perché è nella tabella A1:C2.
• E4 inizierà a scrivere alle ore E4, perché non è presente in nessuna tabella. (A4 inizierebbe a scrivere anche a A4 per gli stessi motivi).

Inoltre, puoi scegliere se sovrascrivere i dati esistenti dopo una tabella o inserire nuove righe per i nuovi dati. Per impostazione predefinita, l'input sovrascrive i dati dopo la tabella. Per scrivere i nuovi dati in nuove righe, utilizza InsertDataOption e specifica insertDataOption=INSERT_ROWS.

Per scoprire di più sui limiti di celle e righe in Fogli, consulta File archiviabili su Google Drive.