How to delete cells from a GSheet programatically?

Question

I have a GSheet that I am using in my Flutter app to record a timestamp of when a user clicks on a button. The timestamp gets stored in column B. Is there a way (I've heard of AppScript but unsure) that I can check my entire google sheet in a single API call that can check the timestamp (column B) and move those rows that are 30 days older than the recorded timestamp to another GSheet?

Answer 1

You would need to check GoogleSheets API documentation, that you would have to adapt with googleapis package (since apparently Google doesnt bother to give example on how it works with .dart...). I will try to edit it with a working example later if nobody provides you an answer.

Edit: ok apparently there is a simple and dedicated way with gsheet package

Example is very clear, let me know if it helps.

A library for working with Google Sheets API v4. Manage your spreadsheets with gsheets in Dart

Tutorial

1/ Create the spreadsheet and save its id

Then save its ID (after d/ and before /edit in the url):

const _spreadsheetId = '1VRxkfV0BpQqwFAb5Orgj-DU7VNfbG78HaZMA-axONEA';

2 / Create Flutter app

Add gsheets in the dependencies of pubspec.yaml

dependencies:
  gsheets: ^0.2.6
  flutter:
    sdk: flutter

followed by flutter pub get.

Copy paste the example from the library:

import 'package:gsheets/gsheets.dart';

// your google auth credentials
const _credentials = r'''
{
  "type": "service_account",
  [...] // you will replace this part later on
}
''';
// your spreadsheet id
const _spreadsheetId = '1VRxkfV0BpQqwFAb5Orgj-DU7VNfbG78HaZMA-axONEA';

void main() async {
  // init GSheets
  final gsheets = GSheets(_credentials);
  // fetch spreadsheet by its id
  final ss = await gsheets.spreadsheet(_spreadsheetId);
  // get worksheet by its title
  var sheet = await ss.worksheetByTitle('example');
  // create worksheet if it does not exist yet
  sheet ??= await ss.addWorksheet('example');

  // update cell at 'B2' by inserting string 'new'
  await sheet.values.insertValue('new', column: 2, row: 2);
  // prints 'new'
  print(await sheet.values.value(column: 2, row: 2));
  // get cell at 'B2' as Cell object
  final cell = await sheet.cells.cell(column: 2, row: 2);
  // prints 'new'
  print(cell.value);
  // update cell at 'B2' by inserting 'new2'
  await cell.post('new2');
  // prints 'new2'
  print(cell.value);
  // also prints 'new2'
  print(await sheet.values.value(column: 2, row: 2));

  // insert list in row #1
  final firstRow = ['index', 'letter', 'number', 'label'];
  await sheet.values.insertRow(1, firstRow);
  // prints [index, letter, number, label]
  print(await sheet.values.row(1));

  // insert list in column 'A', starting from row #2
  final firstColumn = ['0', '1', '2', '3', '4'];
  await sheet.values.insertColumn(1, firstColumn, fromRow: 2);
  // prints [0, 1, 2, 3, 4, 5]
  print(await sheet.values.column(1, fromRow: 2));

  // insert list into column named 'letter'
  final secondColumn = ['a', 'b', 'c', 'd', 'e'];
  await sheet.values.insertColumnByKey('letter', secondColumn);
  // prints [a, b, c, d, e, f]
  print(await sheet.values.columnByKey('letter'));

  // insert map values into column 'C' mapping their keys to column 'A'
  // order of map entries does not matter
  final thirdColumn = {
    '0': '1',
    '1': '2',
    '2': '3',
    '3': '4',
    '4': '5',
  };
  await sheet.values.map.insertColumn(3, thirdColumn, mapTo: 1);
  // prints {index: number, 0: 1, 1: 2, 2: 3, 3: 4, 4: 5, 5: 6}
  print(await sheet.values.map.column(3));

  // insert map values into column named 'label' mapping their keys to column
  // named 'letter'
  // order of map entries does not matter
  final fourthColumn = {
    'a': 'a1',
    'b': 'b2',
    'c': 'c3',
    'd': 'd4',
    'e': 'e5',
  };
  await sheet.values.map.insertColumnByKey(
    'label',
    fourthColumn,
    mapTo: 'letter',
  );
  // prints {a: a1, b: b2, c: c3, d: d4, e: e5, f: f6}
  print(await sheet.values.map.columnByKey('label', mapTo: 'letter'));

  // appends map values as new row at the end mapping their keys to row #1
  // order of map entries does not matter
  final secondRow = {
    'index': '5',
    'letter': 'f',
    'number': '6',
    'label': 'f6',
  };
  await sheet.values.map.appendRow(secondRow);
  // prints {index: 5, letter: f, number: 6, label: f6}
  print(await sheet.values.map.lastRow());

  // get first row as List of Cell objects
  final cellsRow = await sheet.cells.row(1);
  // update each cell's value by adding char '_' at the beginning
  cellsRow.forEach((cell) => cell.value = '_${cell.value}');
  // actually updating sheets cells
  await sheet.cells.insert(cellsRow);
  // prints [_index, _letter, _number, _label]
  print(await sheet.values.row(1));
}

3/ Create a service account and generate a key

go to https://console.cloud.google.com/iam-admin/serviceaccounts and click on create service account. Select your project or create new project. Once the project is selected or created you have to click on create a new service account.

After entering the name of your project click next until you reach the final step: Add Key, it will save a json file on your cpu.

4/ Add the credentials to your project

You copy the content of this json file and you replace the { } in your project with its content

5/ Share the google sheets with the service account email

On the service account page you will notice that your service account has an email. Copy it and go to your spreadsheet, and share your spreadsheet with the service account.

6/ Enable Google Sheets and Google Drive API

If it was not done as it was a new project, you have to enable both APIs for your project, in order to allow your service account to access it. Go to https://console.cloud.google.com/apis/library and search for both Google Sheets API and Google Drive API and add them.

7/ Change code according to your needs

To delete cell content, you can simply write the following code:

 final cell = await sheet.cells.cell(column: 1, row: 1); // select A1
 await cell.post('');   // update cell at 'A1' by inserting ''

To test that it works fine, create sheet named example and enter whatever value in cell A1, run the app and check its effect.

Answer 2

Here is a method that deletes a single row from your sheet in Google sheets having in mind that i have passed an String id to that method as an indicator for that row in the first column in the sheets

 static Future deleteRowfromtheSheet(String id) async {

        if (_googleSheetname == null) return false;
    
        final index = await _googleSheetname!.values.rowIndexOf(id);
        if (index == -1) return false;
    
        return _googleSheetname!.deleteRow(index);
      }

}