1

I need to find a row in my spreadsheet that contains some data and delete it. all I know is the id inside a cell on the row.

How can I perform a search for the location of the cell with google REST API

Like for example: 

GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:find?value=123456

response:
 sheet1! B2:B2

Also: I also want to support cut&paste from one sheet to another. so put metadata on the row can't work because it lost when copying cells from the row to another row. ( there is no REST API to cut&paste dimension (row||column) from one sheet to another.

If there is a way to copy&paste dimension between sheet I simple can use


POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchGetByDataFilter

{
  "dataFilters": [
    {
      object (DataFilter)
    }
  ],
  "majorDimension": enum (Dimension),
  "valueRenderOption": enum (ValueRenderOption),
  "dateTimeRenderOption": enum (DateTimeRenderOption)
}

to find the row and delete it.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
pery mimon
  • 7,713
  • 6
  • 52
  • 57

1 Answers1

2
  • You want to retrieve the row number by searching a value using Sheets API.
  • You want to delete the rows by searching a value using Sheets API.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Issue and workaround:

Unfortunately, in the current stage, the row number cannot be directly retrieved by searching a value with Sheets API. And also, the rows cannot be deleted by searching a value using Sheets API. I'm not sure whether these are added at the future update. So in the current workaround, how about the following workarounds?

Workaround 1:

The flow of this workaround is as follows.

  1. Retrieve all sheet names and sheet ID using the method of "spreadsheets.get" in Sheets API.
  2. Retrieve all values from the sheet in the Spreadsheet using the method of "spreadsheets.values.batchGet" Sheets API.
  3. Search the sheet name and row number from the retrieved values using a value.
    • In this case, it is required to prepare a script.
  4. Delete rows using the method of "spreadsheets.batchUpdate" in Sheets API.

By this flow, your goal can be achieved.

Workaround 2:

In this workaround, Web Apps created by Google Apps Script is used as an API.

1. Create new project of Google Apps Script.

Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.

If you want to directly create it, please access to https://script.new/. In this case, if you are not logged in Google, the log in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

2. Prepare script.

Please copy and paste the following script to the script editor. This script is for the Web Apps. In this script, Sheets API is used. So please enable Sheets API at Advanced Google services.

function doGet(e) {
  var findText = e.parameter.value;
  var spreadsheetId = e.parameter.spreadsheetId;
  var deleteRows = e.parameter.deleteRows && e.parameter.deleteRows.toLowerCase() === "true";
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var ranges = ss.createTextFinder(findText).findAll();
  if (deleteRows === true) {
    var requests = ranges.reverse().map(r => ({deleteDimension:{range:{dimension:"ROWS",sheetId:r.getSheet().getSheetId(),startIndex:r.getRow() - 1,endIndex:r.getRow()}}}));
    Sheets.Spreadsheets.batchUpdate({requests: requests}, spreadsheetId);
  }
  var res = ranges.map(r => ({range: `'${r.getSheet().getSheetName()}'!${r.getA1Notation()}`, sheetId: r.getSheet().getSheetId()}));
  return ContentService.createTextOutput(JSON.stringify(res));
}
  • In this case, the GET method is used. When you want to run the function by giving the large data, you can use doPost() instead of doGet().

3. Deploy Web Apps.

  1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
  2. Select "Me" for "Execute the app as:".
    • By this, the script is run as the owner.
    • Here, when "Anyone" is set, the script is run as each user. In this case, it is required to share the script to each user. And the access token is required to be used. Please be careful this.
  3. Select "Anyone, even anonymous" for "Who has access to the app:".
    • In this case, no access token is required to be request. I think that as the test case, I recommend this setting.
    • Of course, you can also use the access token. At that time, please set this to "Anyone".
  4. Click "Deploy" button as new "Project version".
  5. Automatically open a dialog box of "Authorization required".
    1. Click "Review Permissions".
    2. Select own account.
    3. Click "Advanced" at "This app isn't verified".
    4. Click "Go to ### project name ###(unsafe)"
    5. Click "Allow" button.
  6. Click "OK".
  7. Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.
    • When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

3. Run the function using Web Apps.

This is a sample curl command for using Web Apps. Please set your Web Apps URL, spreadsheetId and the search value.

curl -GL \
  -d "spreadsheetId=###" \
  -d "value=sample" \
  "https://script.google.com/macros/s/###/exec"
  • When spreadsheetId=### and value=sample are used as the query parameter at doGet(e). For example, you can retrieve spreadsheetId using e.parameter.spreadsheetId.

  • At above script of Web Apps, when this curl command is run, the value like [{"range":"'Sheet1'!A1","sheetId":0},{"range":"'Sheet2'!B2","sheetId":###}] is returned. This is the result by searching a value of sample.

    • As a sample case, the value is searched from all sheets in the Spreadsheet. About this, you can modify the script for your actual situation.

  • If deleteRows=true is used like below, the value is searched from all sheets in the Spreadsheet and the searched rows are deleted.

    curl -GL \
  -d "spreadsheetId=###" \
  -d "value=sample" \
  -d "deleteRows=true" \
  "https://script.google.com/macros/s/###/exec"

Note:

  • This sample script is used with V8.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • ok. nice. there is something to read here about the ability of google script app. the first method can do the work but it not safe because the operations are not atomics. So in the time I got all the data and search inside it someone can move the row. sure I can put protected on the cells from the time I start until I finish. But the other approach with the ability of self-searching sound promising. – pery mimon Mar 20 '20 at 09:43
  • @pery mimon Thank you for replying. For example, when the query language is used, the values of searched rows can be retrieved. [Ref](https://stackoverflow.com/a/60338371) But the row number cannot be directly retrieved. So I proposed to use Web Apps as an API. – Tanaike Mar 20 '20 at 11:57
  • @pery mimon Also I saw your additional question. Before I think of your new question, I would like to confirm whether my answer was not useful for your 1st question. If my answer for your 1st question is not suitable, at first, I have to resolve your 1st question. So can I ask you about it? After your 1st question was resolved, I would like to think of your new question. If I misunderstood your replying and questions, I apologize. – Tanaike Mar 20 '20 at 11:57
  • thanks @Tanaike. you Workaround 1. can be work but it involve to many async steps . and I feel it not safe to use in a collaborative environment. Workaround 2 need more reaserch from me to see if it easy as custrom googlesheet api. I am not asking new question just adding more details/views to my real needs – pery mimon Mar 21 '20 at 08:43
  • @pery mimon Thank you for replying. From your replying, about `Workaround 2 need more reaserch from me to see if it easy as custrom googlesheet api.`, I would like to wait for the result of your research. – Tanaike Mar 21 '20 at 23:10